﻿using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Word;
using System;
using System.Collections.Generic;
using System.IO;
using System.Reflection;
using System.Text;
using System.Windows.Forms;
using Tp.GUI.Dialog;
using Tp.Properties;
using TpBaseObjects.Base;
using TpBaseObjects.Models.BusinessCases;
using TpBaseObjects.Objects;
using TpContracts;
using TpData.Manager;
using TpExtensions;
using TpTools;
using myOffice = Microsoft.Office.Interop;

namespace Tp {
    class Core {

        #region Members

        public enum ButtonDefaultValues {
            Button1 = 1,
            Button2 = 2
        }

        private enum faceIDs {
            NewSheet = 343,
            Export = 352,
            Import = 342,
            Dokument = 341,
            Bearbeitung = 345,
            Funktion = 341
        }

        public enum Customer {
            Verkauf = 1,
            Kunde = 2,
            Verloren = 3,
            Rewe = 4
        }

        private const string errType = "Err";
        private const string logType = "Log";
        private const string logArea = "Tp.Core";

        #endregion

        #region Kontextmenü


        #region Neues Arbeitsblatt

        public static void Abnahmeprotokoll(Workbook wb, Worksheet sh, Worksheet ap, bool isRep) {
            try {

                #region Members

                ProjectSheet sheet = new ProjectSheet(sh, true, true);
                int startRow = Settings.Default.ZeileStart;
                int copyRow = Settings.Default.ZeileStartAbnahmeprotokoll;

                int copyRowRepPauschal = Settings.Default.ZeileStartAbnahmeprotokollReparaturPauschal;
                int copyRowRepRegie = Settings.Default.ZeileStartAbnahmeprotokollReparaturRegie;
                int copyRowRepProdukte = Settings.Default.ZeileStartAbnahmeprotokollReparaturProdukte;


                #endregion

                #region SetData

                Organisation org = OrganisationDataManager.LoadOrganisation(sheet.Kundenname, sheet.Suchbegriff);
                if (!CheckOrgData(org, sheet.Suchbegriff, wb.Name)) {
                    return;
                }

                SetDataAbnahmeprotokoll(org, ap, sheet, isRep);

                #endregion

                bool startRowSelect = false;
                while (startRowSelect == false & startRow < 10000) {
                    myOffice.Excel.Range row = sh.Rows[startRow];
                    ProjectRow selRow = new ProjectRow(row);

                    if (selRow.RowType.ToUpper() == "DB") {
                        LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Abnahmeprotokoll: " + wb.Name + "(" + ap.Name + ")", false);
                        return;
                    }

                    if (selRow.RowLeistung == "1") {
                        string strSelect = (selRow.RowType == null ? "" : ((string)selRow.RowType).ToUpper());
                        switch (strSelect) {
                            case "LE":
                            case "PR":
                                if (isRep) {
                                    if (selRow.Einheit == "Pa") {
                                        CreateApRepZeile(row, ap, copyRowRepPauschal);
                                        copyRowRepPauschal++;
                                    }
                                    if (selRow.Einheit == "Std") {
                                        CreateApRepZeile(row, ap, copyRowRepRegie);
                                        copyRowRepRegie++;
                                    }
                                    if (selRow.Einheit == "Stk") {
                                        CreateApRepZeile(row, ap, copyRowRepProdukte);
                                        copyRowRepProdukte++;
                                    }
                                } else {
                                    if ((copyRow > Settings.Default.ZeileApMaxFirstPage) & (copyRow < Settings.Default.ZeileApStartNextPage)) {
                                        copyRow = Settings.Default.ZeileApStartNextPage;
                                    }

                                    CreateApZeile(row, ap, copyRow);
                                    copyRow++;
                                }
                                break;
                            case "DB":
                                startRowSelect = true;
                                break;
                            default:
                                break;
                        }
                        selRow.RowLeistung = "";
                    }
                    startRow++;

                }
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "Abnahmeprotokoll: " + ex.Message, true);
            }
        }

        public static void Abrechnung(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet abrechnung) {
            ProjectSheet sheet = new ProjectSheet(abrechnung, true, true);
            bool setProvision = false;

            #region Check Is a Calc Sheet

            if (!sheet.IsCalcSheet) {
                return;
            }

            #endregion

            try {

                #region Set rows

                foreach (ProjectRow row in sheet.MyRows) {
                    myOffice.Excel.Range r1 = abrechnung.Rows[row.RowNumber];
                    if (!row.IsSummenZeile & !row.IsRabattZeile & row.RowType != "") {
                        if (row.JobDatum != new DateTime(1900, 1, 1)) {
                            if (row.JobDatum.Date <= DateTime.Now.Date) {
                                //r1.Font.Name = Extensions.tpFontNormal;
                                Extensions.SetFontBoldOrNormal(r1, Extensions.tpFont.Regular);
                            } else {
                                //r1.Font.Name = Extensions.tpFontBold;
                                Extensions.SetFontBoldOrNormal(r1, Extensions.tpFont.Bold);
                            }
                        } else {
                            //r1.Font.Name = Extensions.tpFontBold;
                            Extensions.SetFontBoldOrNormal(r1, Extensions.tpFont.Bold);
                        }
                    }

                    if (row.SummenText.ToUpper() == "PROVISION VOM DB") {
                        try {
                            setProvision = true;
                            myOffice.Excel.Range rowSel = abrechnung.Rows[row.RowNumber];
                            myOffice.Excel.Range sumVP = rowSel.Cells[1, Settings.Default.SpalteSummeVp];
                            sumVP.FormulaR1C1 = sumVP.Value;
                            sumVP.Font.Color = Settings.Default.Rot;
                            myOffice.Excel.Range db = rowSel.Cells[1, Settings.Default.SpalteDb];
                            db.FormulaR1C1 = "=R" + row.RowNumber.ToString() + "C" + Settings.Default.SpalteSummeVp.ToString() + "/R" + (row.RowNumber - 1).ToString() + "C" + Settings.Default.SpalteSummeVp.ToString() + "*-100";
                            db.Font.ThemeColor = XlThemeColor.xlThemeColorAccent2;
                        } catch (Exception ex) {
                            LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "Abrechnung: Provision vom DB: " + ex.Message, true);
                        }
                    }

                #endregion

                }

            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "Abrechnung: " + ex.Message, true);
            }
            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Abrechnung: (" + (setProvision ? "1" : "0") + ")" + wb.Name + "(" + abrechnung.Name + ")", false);
        }

        public static void AngebotNeu(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, Worksheet angebot) {
            try {
                if (!IsCalcSheet(sh)) {
                    return;
                }

                #region Global

                CopyFaktoren(sh, angebot);
                GlobalVar.SummenStartRow = Settings.Default.StartCopyZeile;
                GlobalVar.SummenStartRow2 = Settings.Default.StartCopyZeile;
                GlobalVar.SummenStartRow3 = Settings.Default.StartCopyZeile;
                GlobalVar.MaxSummenHierachie = 0;
                GlobalVar.CopyRabatt = true;
                GlobalVar.RemoveAllProduktZeilen();
                GlobalVar.RemoveAllSummenZeilen();
                GlobalVar.ClearRabatte();

                #endregion

                #region Members

                int startRow = Settings.Default.ZeileStart;
                int copyRow = Settings.Default.StartCopyZeile;
                int startSummenRow = startRow;
                int lastSummenRow = 0;

                #endregion

                bool startRowSelect = false;
                myOffice.Excel.Range r1 = angebot.Cells[1, "C"];
                r1.Value = sh.Name;

                while (startRowSelect == false & startRow < 10000) {
                    ((Microsoft.Office.Interop.Excel._Worksheet)sh).Activate();
                    startSummenRow = GlobalVar.LastSummenRow;
                    myOffice.Excel.Range row = sh.Rows[startRow];
                    ProjectRow selRow = new ProjectRow(row);

                    string strSelect = (selRow.RowType == null ? "" : ((string)selRow.RowType).ToUpper());
                    switch (strSelect) {
                        case "LE":
                        case "PR":
                            CopyProduktZeile(row, angebot, copyRow, false);
                            copyRow++;
                            startRow++;
                            break;
                        case "RA":
                            if (CopyRabattZeile(row, angebot, copyRow)) {
                                GlobalVar.AddRabatt(row.Address.ToString());
                                copyRow++;
                            }
                            startRow++;
                            break;
                        case "S":
                        case "SU":
                        case "S4":
                            if (CreateSummenZeile(row, selRow, sh, angebot, copyRow, lastSummenRow)) {
                                copyRow++;
                            }
                            startRow++;
                            break;
                        case "DB":
                            CreateDifference(sh, angebot, copyRow);
                            startRowSelect = true;
                            break;
                        default:
                            startRow++;
                            break;
                    }
                }
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "AngebotNeu: " + ex.Message, true);
            } finally {
                //Range r1 = angebot.Columns["AH"];
                //r1.ColumnWidth = Extensions.tpColumnWidth;
                SetKontextMenu(exApp, wb, angebot);
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "AngebotNeu: " + wb.Name + "(" + angebot.Name + ")", false);
            }
        }

        public static void Kalkulation(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, Worksheet ka) {

            ProjectSheet q = new ProjectSheet(sh, true, true, false);
            ProjectSheet kalk = new ProjectSheet(ka, false);
            kalk.OrgName = q.OrgName;
            kalk.Projekt = q.Projekt;
            kalk.Plz = q.Plz;
            kalk.Ort = q.Ort;
            kalk.Straße = q.Straße;
            kalk.Kilometer = q.Kilometer;

            foreach (ProjectRow row in q.MyRows) {
                if (row.JobExported) {
                    kalk.WriteJob(row.Job, row.JobDetail, row.JobDatum, row.JobOrt, row.Durchführender1, row.RechnungsNummer, row.HyperlinkPath, row.Hyperlink, row.Durchführender2, true);
                }
            }

            wb.Save();

        }

        public static void LieferscheinNeu(Workbook wb, Worksheet sh, Worksheet ls) {
            try {

                #region Members

                ProjectSheet sheet = new ProjectSheet(sh, false);
                int startRow = Settings.Default.ZeileStart;
                int copyRow = Settings.Default.ZeileStartLieferschein;

                #endregion

                #region SetData

                Organisation org = OrganisationDataManager.LoadOrganisation(sheet.Kundenname, sheet.Suchbegriff);
                if (!CheckOrgData(org, sheet.Suchbegriff, wb.Name)) {
                    return;
                }
                SetDataLieferschein(org, ls);

                #endregion

                bool startRowSelect = false;
                while (startRowSelect == false & startRow < 10000) {
                    myOffice.Excel.Range row = sh.Rows[startRow];
                    ProjectRow selRow = new ProjectRow(row);

                    if (Tools.Nz(selRow.RowType, "").ToUpper() == "DB") {
                        LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "LieferscheinNeu: " + wb.Name + "(" + ls.Name + ")", false);
                        return;
                    }

                    if (Tools.Nz(selRow.RowLeistung, "") == "1") {
                        string strSelect = (selRow.RowType == null ? "" : ((string)selRow.RowType).ToUpper());
                        switch (strSelect) {
                            case "LE":
                            case "PR":
                                CreateLsZeile(row, ls, copyRow);
                                copyRow++;
                                break;
                            case "DB":
                                startRowSelect = true;
                                break;
                            default:
                                break;
                        }
                        selRow.RowLeistung = "";
                    }
                    startRow++;

                }
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "LieferscheinNeu: " + ex.Message, true);
            }

        }

        public static void LoadFiles(InvoiceObject i) {
            string filePath = Settings.Default.PathKunden + i.ProjektBezeichnung.Left(2) + @"\" + i.Kunde.Left(1) + @"\";
            string searchPattern = i.Kunde + i.ProjektBezeichnung + "??" + "ls??.*";
            string[] myFiles = Directory.GetFiles(filePath, searchPattern);

            if (myFiles.Length > 0) {
                List<string> file = new List<string>();
                foreach (string fileName in myFiles) {
                    file.Add(fileName);
                }
                i.Lieferscheine = file;
            }

            searchPattern = i.Kunde + i.ProjektBezeichnung + "??" + "ap??.*";
            myFiles = Directory.GetFiles(filePath, searchPattern);
            if (myFiles.Length > 0) {
                List<string> file = new List<string>();
                foreach (string fileName in myFiles) {
                    file.Add(fileName);
                }
                i.Abnahmeprotokolle = file;
            }
        }

        public static void ProjektBerichtNeu(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, Worksheet pb) {
            try {
                ProjectSheet sheet = new ProjectSheet(sh, false);
                Organisation org = OrganisationDataManager.LoadOrganisation(sheet.Kundenname, sheet.Suchbegriff);
                if (!CheckOrgData(org, sheet.Suchbegriff, wb.Name)) {
                    return;
                }
                SetDataProjektBericht(org, pb, sheet);

                int startRow = Settings.Default.ZeileStart;
                int copyRow = Settings.Default.ProjektberichtStartZeile;
                ((Microsoft.Office.Interop.Excel._Worksheet)sh).Activate();

                //Debug.Print("Start Pb : {0}", DateTime.Now);
                bool weiter = false;
                while (weiter == false & startRow < 10000) {

                    myOffice.Excel.Range row = sh.Rows[startRow];

                    ProjectRow shRow = new ProjectRow(row);
                    if (Tools.Nz(shRow.RowLeistung, "") == "1") {
                        if (shRow.IsLeOrLa) {
                            //Leistung
                            myOffice.Excel.Range r1 = pb.Cells[copyRow, "A"];
                            r1.Value = shRow.Leistung;

                            //Einheit
                            r1 = pb.Cells[copyRow, "U"];
                            r1.Value = shRow.Einheit;

                            //Menge
                            r1 = pb.Cells[copyRow, "V"];
                            r1.Value = shRow.Menge;

                            //Pauschal-Zeit
                            r1 = pb.Cells[copyRow, "W"];
                            r1.Value = shRow.LeistungZeit;

                            //Leistung% AK: 12-04-25 Es, lt. Besprechung vom 12-04-20
                            //r1 = pb.Cells[copyRow, Settings.Default.ProjektberichtSpalteLeistungProzent];
                            //r1.Value = shRow.LeistungProzent;

                            //if(shRow.Einheit.ToUpper() == "PA") {
                            //    calcTime += shRow.LeistungZeit;
                            //}
                            shRow.RowLeistung = "";

                            copyRow++;
                        }
                        shRow.RowLeistung = "";
                    }
                    startRow++;
                    if (Tools.Nz(shRow.RowType, "").ToUpper() == "DB") {
                        weiter = true;
                    }
                }

                //if(calcTime > 0) {
                //    Range r1 = pb.Range[Settings.Default.ZellePbPauschZeit];
                //    r1.Value = calcTime;
                //}

            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "ProjektberichtNeu: " + ex.Message, true);
            } finally {
                SetKontextMenu(exApp, wb, pb);
                ((Microsoft.Office.Interop.Excel._Worksheet)pb).Activate();
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "ProjektberichtNeu: " + wb.Name + "(" + pb.Name + ")", false);
            }
        }

        public static void ProjektBerichtLohnfertigungNeu(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, Worksheet pb) {
            try {
                int anzRows = 0;
                ProjectSheet sheet = new ProjectSheet(sh, false);
                Organisation org = OrganisationDataManager.LoadOrganisation(sheet.Kundenname, sheet.Suchbegriff);
                if (!CheckOrgData(org, sheet.Suchbegriff, wb.Name)) {
                    return;
                }

                SetDataLohnfertigungBericht(org, pb, sheet);

                int startRow = Settings.Default.ZeileStart;
                int copyRow = 5;
                ((Microsoft.Office.Interop.Excel._Worksheet)sh).Activate();


                //Debug.Print("Start Pb : {0}", DateTime.Now);
                bool weiter = false;
                while (weiter == false & startRow < 10000) {

                    myOffice.Excel.Range row = sh.Rows[startRow];
                    myOffice.Excel.Range checkBasis = sh.Cells[startRow, "AF"];
                    myOffice.Excel.Range sum = sh.Cells[startRow, "A"];

                    if (checkBasis.Value != null) {
                        if (sum.Value != null) {
                            if (sum.Value.ToString().ToUpper() == "SU" && checkBasis.Value.ToString().Contains("Basisleistung") && copyRow < 10) {
                                copyRow = 10;
                            }
                        }
                    }

                    ProjectRow shRow = new ProjectRow(row);
                    if (Tools.Nz(shRow.RowLeistung, "") == "1") {
                        if (shRow.IsLeOrLa) {
                            if (anzRows < 31) {


                                if (copyRow >= 10) {
                                    anzRows += 1;
                                }

                                //Leistung
                                myOffice.Excel.Range r1 = pb.Cells[copyRow, "A"];
                                r1.Value = shRow.Leistung;

                                //Einheit
                                r1 = pb.Cells[copyRow, "W"];
                                r1.Value = shRow.Menge;

                                //Menge
                                r1 = pb.Cells[copyRow, "X"];
                                r1.Value = shRow.RechnungsNummer;

                                //Pauschal-Zeit
                                r1 = pb.Cells[copyRow, "Y"];
                                r1.Value = shRow.PauschalZeit;

                                //Summe
                                r1 = pb.Cells[copyRow, "Z"];
                                r1.Value = shRow.LeistungZeit;

                                shRow.RowLeistung = "";
                            }
                            copyRow++;
                        }
                        shRow.RowLeistung = "";
                    }
                    startRow++;
                    if (Tools.Nz(shRow.RowType, "").ToUpper() == "DB") {
                        weiter = true;
                    }
                }

                //if(calcTime > 0) {
                //    Range r1 = pb.Range[Settings.Default.ZellePbPauschZeit];
                //    r1.Value = calcTime;
                //}

            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "ProjektberichtNeu: " + ex.Message, true);
            } finally {
                SetKontextMenu(exApp, wb, pb);
                ((Microsoft.Office.Interop.Excel._Worksheet)pb).Activate();
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "ProjektberichtNeu: " + wb.Name + "(" + pb.Name + ")", false);
            }
        }

        public static void ProjektNeu(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {
            string projektname = "";
            try {
                int sheetCount = wb.Sheets.Count;
                int newProjectID = GetMaxProjectNo(wb) + 1;
                string lastSheet = GetLastSheetName(wb);
                Worksheet af = wb.Sheets[lastSheet];
                Worksheet calc = GetCalcSheet(wb);
                Worksheet proj = wb.Sheets.Add(After: af, Type: Settings.Default.VorlageExcelKalkulation);
                proj.Name = "P" + newProjectID.ToString() + "k1";
                projektname = proj.Name;

                ProjektWorkbook pw = new ProjektWorkbook(newProjectID, wb);

                if (calc != null) {
                    proj.Range[Settings.Default.ZelleKundenname].Value = calc.Range[Settings.Default.ZelleKundenname].Value;
                    proj.Range[Settings.Default.ZellePlz].Value = calc.Range[Settings.Default.ZellePlz].Value;
                    proj.Range[Settings.Default.ZelleOrt].Value = calc.Range[Settings.Default.ZelleOrt].Value;
                    proj.Range[Settings.Default.ZelleStrasse].Value = calc.Range[Settings.Default.ZelleStrasse].Value;
                    proj.Range[Settings.Default.ZelleKilometer].Value = calc.Range[Settings.Default.ZelleKilometer].Value;
                    proj.Range[Settings.Default.ZelleProjektbezeichnung].Value = pw.MyFileName + "p" + newProjectID.ToString();
                }
                //CreateNewCalc(proj);
                SetKontextMenu(exApp, wb, proj);
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "ProjektNeu: " + ex.Message, true);
            } finally {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "ProjektNeu: " + wb.Name + "(" + projektname + ")", false);
            }
        }

        public static void RechnungNeu(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, Worksheet rechnung) {
            try {
                if (!IsCalcSheet(sh)) {
                    return;
                }

                #region Global

                CopyFaktoren(sh, rechnung);
                GlobalVar.SummenStartRow = Settings.Default.StartCopyZeile;
                GlobalVar.SummenStartRow2 = Settings.Default.StartCopyZeile;
                GlobalVar.SummenStartRow3 = Settings.Default.StartCopyZeile;
                GlobalVar.MaxSummenHierachie = 0;
                GlobalVar.CopyRabatt = true;
                GlobalVar.RemoveAllProduktZeilen();
                GlobalVar.RemoveAllSummenZeilen();
                GlobalVar.ClearRabatte();

                #endregion

                #region Members

                int startRow = Settings.Default.ZeileStart;
                int copyRow = Settings.Default.StartCopyZeile;
                int startSummenRow = startRow;
                int lastSummenRow = 0;
                int productRows = 0;
                ProjectSheet ps = new ProjectSheet(sh, false);

                #endregion

                if (TpTools.Tools.ExistsContentInColumn(sh, "BB", "1")) {
                    bool startRowSelect = false;
                    while (startRowSelect == false & startRow < 10000) {
                        ((Microsoft.Office.Interop.Excel._Worksheet)sh).Activate();
                        startSummenRow = GlobalVar.LastSummenRow;
                        myOffice.Excel.Range row = sh.Rows[startRow];
                        ProjectRow selRow = new ProjectRow(row);

                        if (Tools.Nz(selRow.RowInvoiceMarkup, "") == "1" | Tools.Nz(selRow.RowType, "").ToUpper() == "RA" | Tools.Nz(selRow.RowType, "").ToUpper() == "S4" | Tools.Nz(selRow.RowType, "").ToUpper() == "DB") {
                            string strSelect = (selRow.RowType == null ? "" : ((string)selRow.RowType).ToUpper());
                            switch (strSelect) {
                                case "LE":
                                case "PA":
                                case "PR":
                                    CopyProduktZeile(row, rechnung, copyRow, false);
                                    selRow.RowInvoiceMarkup = "Ar";
                                    //selRow.SetExported("Ar");
                                    copyRow++;
                                    productRows++;
                                    break;
                                case "RA":
                                    if (selRow.Ust || selRow.RabattProzent != 0.0) {
                                        if (CopyRabattZeile(row, rechnung, copyRow)) {
                                            //GlobalVar.AddRabatt(row.Address.ToString());
                                            copyRow++;
                                        }
                                    }
                                    break;
                                case "S4":
                                    bool visibleRow = true;
                                    if ((selRow.IsSummeGesamt && ps.SummeRabatte == 0.0) || (((selRow.IsSummeGesamt || selRow.IsSummeNetto) && productRows <= 1))) {
                                        visibleRow = false;
                                    }
                                    if (CreateSummenZeile(row, selRow, sh, rechnung, copyRow, lastSummenRow, visibleRow)) {
                                        copyRow++;
                                    }
                                    break;
                                case "DB":
                                    CreateRechnungsDifference(wb, sh, rechnung, copyRow);
                                    startRowSelect = true;
                                    break;
                                default:
                                    break;
                            }
                            selRow.RowLeistung = "";
                        }
                        startRow++;
                    }
                } else {

                    bool startRowSelect = false;
                    while (startRowSelect == false & startRow < 10000) {
                        ((Microsoft.Office.Interop.Excel._Worksheet)sh).Activate();
                        startSummenRow = GlobalVar.LastSummenRow;
                        myOffice.Excel.Range row = sh.Rows[startRow];
                        ProjectRow selRow = new ProjectRow(row);


                        string strSelect = (selRow.RowType == null ? "" : ((string)selRow.RowType).ToUpper());
                        switch (strSelect) {
                            case "LE":
                            case "PR":
                                if (!selRow.RechnungExported) {
                                    CopyProduktZeile(row, rechnung, copyRow, false);
                                    copyRow++;
                                }
                                productRows++;
                                startRow++;
                                break;
                            case "RA":
                                if (CopyRabattZeile(row, rechnung, copyRow)) {
                                    GlobalVar.AddRabatt(row.Address.ToString());
                                    GlobalVar.CopyRabatt = true;
                                    copyRow++;
                                }
                                startRow++;
                                break;
                            case "S":
                            case "SU":
                            case "S4":
                                bool visibleRow = true;
                                if ((selRow.IsSummeGesamt && ps.SummeRabatte == 0.0) || (((selRow.IsSummeGesamt || selRow.IsSummeNetto) && productRows <= 1))) {
                                    visibleRow = false;
                                }
                                if (CreateSummenZeile(row, selRow, sh, rechnung, copyRow, lastSummenRow, visibleRow)) {
                                    copyRow++;
                                }
                                startRow++;
                                break;
                            case "DB":
                                CreateRechnungsDifference(wb, sh, rechnung, copyRow);
                                startRowSelect = true;
                                break;
                            default:
                                startRow++;
                                break;
                        }

                    }
                }

                ProjectSheet s1 = new ProjectSheet(sh, false);
                if (s1.IsReparatur) {
                    myOffice.Excel.Range r1 = rechnung.Columns["AO"];
                    r1.NumberFormat = "0.00";
                }
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "RechnungNeu: " + ex.Message, true);
            } finally {
                SetKontextMenu(exApp, wb, rechnung);
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "RechnungNeu: " + wb.Name + "(" + rechnung.Name + ")", false);
            }
        }

        #endregion

        #region Export + Funktion

        public static void AbrechnungLieferant(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {
            ProjectSheet sheet = new ProjectSheet(sh, false);
            int startRow = 301;
            bool weiter = false;
            myOffice.Excel.Range r1;
            myOffice.Excel.Range row;
            List<AbrLiefRowObj> myList = new List<AbrLiefRowObj>();
            if (sheet.IsCalcSheet) {
                try {

                    //Get Rows from source
                    #region GetRows
                    string curAr = "";

                    while (weiter == false && startRow < 2000) {
                        r1 = sh.Cells[startRow, "A"];
                        if (r1.Value != null) {
                            if (r1.Value.ToString().ToUpper() == "DB") {
                                break;
                            }
                        }
                        row = sh.Rows[startRow, Missing.Value];
                        
                        ProjectRow selRow = new ProjectRow(row);
                        if (selRow.RowInvoiceMarkup == "1") {
                            AbrLiefRowObj abrRow = new AbrLiefRowObj();
                            abrRow.AuftragNummer = selRow.Auftragsnummer;
                            abrRow.BestellDatum = selRow.Bestelldatum;
                            abrRow.BestellNummer = selRow.BestellNr;
                            abrRow.Einheit = selRow.Einheit;
                            abrRow.EinzelPreis = selRow.Einzelpreis;
                            abrRow.EkVorSK = selRow.EkVorSk;
                            abrRow.InUse = true;
                            abrRow.InvoiceAr = selRow.AusgangsrechnungsNr;
                            abrRow.InvoiceEr = selRow.EingangsrechnungsNr; 
                            abrRow.Kunde = selRow.KundenName;
                            abrRow.Menge = selRow.Menge;
                            abrRow.Produkt = selRow.Leistung;
                            abrRow.Project = selRow.ProjectName;
                            abrRow.SummePreis = selRow.SummeEp;
                            abrRow.RowNumber = selRow.CurrentRowNumber;
                            abrRow.Lieferant = selRow.Lieferant;
                            if (curAr == "") {
                                curAr = selRow.BestellNr;
                            }
                            myList.Add(abrRow);
                        }
                        startRow++;
                    }

                    startRow = 301;

                    if (myList.Count > 0) {
                        while (weiter == false && startRow < 2000) {
                            r1 = sh.Cells[startRow, "A"];
                            if (r1.Value != null) {
                                if (r1.Value.ToString().ToUpper() == "DB") {
                                    break;
                                }
                            }
                            row = sh.Rows[startRow, Missing.Value];

                            ProjectRow selRow = new ProjectRow(row);
                            if (selRow.RowInvoiceMarkup != "1" && selRow.BestellNr == curAr) {
                                AbrLiefRowObj abrRow = new AbrLiefRowObj();
                                abrRow.AuftragNummer = selRow.Auftragsnummer;
                                abrRow.BestellDatum = selRow.Bestelldatum;
                                abrRow.BestellNummer = selRow.BestellNr;
                                abrRow.Einheit = selRow.Einheit;
                                abrRow.EinzelPreis = selRow.Einzelpreis;
                                abrRow.EkVorSK = selRow.EkVorSk;
                                abrRow.InUse = false;
                                abrRow.InvoiceAr = selRow.AusgangsrechnungsNr;
                                abrRow.InvoiceEr = selRow.EingangsrechnungsNr;
                                abrRow.Kunde = selRow.KundenName;
                                abrRow.Menge = selRow.Menge;
                                abrRow.Produkt = selRow.Leistung;
                                abrRow.Project = selRow.ProjectName;
                                abrRow.SummePreis = selRow.SummeEp;
                                abrRow.RowNumber = selRow.CurrentRowNumber;
                                abrRow.Lieferant = selRow.Lieferant;
                                myList.Add(abrRow);
                            }
                            startRow++;
                        }
                    }

                    #endregion

                    #region Check Rows

                    if (myList.Count == 0) {
                        MessageBox.Show("Es wurden keine Zeilen in Spalte BB mit 1 markiert.", "TecOffice", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        return;
                    }

                    #endregion
                    
                    #region Open Form

                    AbrLiefControllForm abrForm = new AbrLiefControllForm(myList);
                    abrForm.ShowDialog();
                    if (abrForm.Cancel) {
                        return;
                    }

                    #endregion

                    #region Remark

                    List<AbrLiefRowObj> curAbrRows = abrForm.MyDetailsModel;
                    foreach (AbrLiefRowObj markRow in curAbrRows) {
                        myOffice.Excel.Range rm1 = sh.Rows[markRow.RowNumber, Missing.Value];
                        ProjectRow selRow = new ProjectRow(rm1);
                        selRow.AusgangsrechnungsNr = markRow.InvoiceAr;
                        selRow.EingangsrechnungsNr = markRow.InvoiceEr;
                        selRow.RowInvoiceMarkup = "Er";
                        selRow.MyRow.Font.Bold = false;
                    }

                    string ma = Office.GetMaForAbrLief(abrForm.MyModel.EingangsReNr.Right(2));
                    
                    if (ma != "") {
                        string provider = "";
                        //ProjectSheet abr = new ProjectSheet(sh, false);
                        //abr.WriteJob("Abrechnung",
                    }

                    #endregion

                    #region Message

                    MessageBox.Show("Abrechnung Lieferant wurde abgeschlossen!", "TecOffice", MessageBoxButtons.OK, MessageBoxIcon.Information);

                    #endregion

                } catch (Exception ex) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "AbrechnungLieferant: " + ex.Message, true);
                } finally {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "AbrechnungLieferant: " + wb.Name + "(" + sh.Name + ")", false);
                }
            }
        }

        public static void ExportAepü(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {
            ProjectSheet sheet = new ProjectSheet(sh, false);
            if (sheet.IsCalcSheet) {
                Workbook ae = null;
                try {
                    #region Check File exists

                    if (!File.Exists(Settings.Default.DateiAeSub)) {
                        string msg = String.Format(Resources.DateiIstNichtVorhanden, Settings.Default.DateiAeSub);
                        MessageBox.Show(msg, wb.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }

                    #endregion

                    #region Check File is in use

                    if (Tools.FileIsInUse(Settings.Default.DateiAeSub)) {
                        string msg = String.Format(Resources.DateiIstBereitsGeöffnet, Settings.Default.DateiAeSub);
                        MessageBox.Show(msg, wb.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }

                    #endregion

                    ae = exApp.Workbooks.Open(Settings.Default.DateiAeSub);
                    Worksheet aeSheet = ae.Sheets["AeSub"];
                    ProjectSheet mySheet = new ProjectSheet(sh, false);

                    int copyRow = 3;
                    myOffice.Excel.Range row = GetFirstAeSubFreeRow(copyRow, aeSheet);
                    AeRow myRow = new AeRow(row);

                    UpdateAeDetails(mySheet, myRow);

                    string msg1 = String.Format(Resources.ExportOk, "(Aepü)");
                    MessageBox.Show(msg1, wb.Name, MessageBoxButtons.OK, MessageBoxIcon.Asterisk);

                } catch (Exception ex) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "ExportAepü: " + ex.Message, true);
                } finally {
                    if (ae != null) {
                        ae.Save();
                        ae.Close();
                    }
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "ExportAepü: " + wb.Name + "(" + sh.Name + ")", false);
                }

            }
        }

        public static void ExportDiäten(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, myOffice.Excel.Range row, string user) {

            #region Members

            ProjectRow r1 = new ProjectRow(row);
            string targetFile = Settings.Default.PathMitarbeiter + r1.JobDatum.Year.ToString().Right(2) + @"\" + user + "sp" + r1.JobDatum.Year.ToString().Right(2) + ".xlsx";
            string sheetName = r1.JobDatum.Month.ToString("00");
            double von = r1.JobTime;
            double bis = r1.JobEndTime;
            string project = r1.OrganisationName + r1.ProjectName;
            DateTime toDate = r1.JobDatum;
            int startRow = 3;
            bool weiter = false;

            Workbook spWB;
            Worksheet spSH;

            #endregion

            #region Check File exists

            if (!File.Exists(targetFile)) {
                string msg = String.Format(Resources.DateiIstNichtVorhanden, targetFile);
                MessageBox.Show(msg, wb.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            #endregion

            #region Check File is in use

            if (Tools.FileIsInUse(targetFile)) {
                string msg = String.Format(Resources.DateiIstBereitsGeöffnet, targetFile);
                MessageBox.Show(msg, wb.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            #endregion

            if (Office.WorkbookIsLoaded(exApp, targetFile)) {
                spWB = exApp.Workbooks[targetFile];
            } else {
                spWB = exApp.Workbooks.Open(Path.Combine(targetFile));
            }

            #region Check WorkBook

            if (!Tools.SheetsExists(spWB, sheetName)) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "Sheet : " + sheetName + " not found.", true);
                string msg = "Arbeitsblatt (" + sheetName + ") in Datei " + Path.GetFileName(targetFile) + " nicht gefunden!";
                MessageBox.Show(msg, "Export Diäten", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            #endregion

            spSH = spWB.Sheets[r1.JobDatum.Month.ToString("00")];

            #region Check WorkSheet

            if (spSH == null) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "Sheet : " + sheetName + " konnte nicht zugeordnet werden!", true);
                string msg = "Arbeitsblatt (" + sheetName + ") in Datei " + Path.GetFileName(targetFile) + " konnte nicht zugeordnet werden!";
                MessageBox.Show(msg, "Export Diäten", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            #endregion

            while (weiter == false) {
                myOffice.Excel.Range r2 = spSH.Rows[startRow, Missing.Value];

                SpesenRow spRow = new SpesenRow(r2);
                if (spRow.IsEndRow) {
                    weiter = true;
                } else {
                    if (spRow.Datum.Date == toDate.Date) {
                        if (spRow.Von > von || spRow.Von == 0.0) {
                            spRow.Von = von;
                        }
                        if (spRow.Bis < bis) {
                            spRow.Bis = bis;
                        }
                        if (!spRow.Project.Contains(project)) {
                            if (spRow.Project == "") {
                                spRow.Project += project;
                            } else {
                                spRow.Project += ", " + project;
                            }
                        }
                    }
                }
                startRow++;
            }
            spWB.Save();
            spWB.Close(true);
        }

        public static void ExportJobevidenz(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {

            ProjectSheet sheet = new ProjectSheet(sh, false);
            if (sheet.IsCalcSheet) {
                Workbook jeWb = null;
                Worksheet jeSheet = null;


                #region Check File exists

                if (!File.Exists(Settings.Default.DateiJesu)) {
                    string msg = String.Format(Resources.DateiIstNichtVorhanden, Settings.Default.DateiJesu);
                    MessageBox.Show(msg, wb.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                #endregion

                #region Check File is in use

                if (Tools.FileIsInUse(Settings.Default.DateiJesu)) {
                    string msg = String.Format(Resources.DateiIstBereitsGeöffnet, Settings.Default.DateiJesu);
                    MessageBox.Show(msg, wb.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                #endregion

                jeWb = exApp.Workbooks.Open(Settings.Default.DateiJesu);
                bool exported = false;
                int startRow = Settings.Default.ZeileStart;
                int startCopyRow = 3;
                try {
                    bool startRowSelect = false;
                    while (startRowSelect == false & startRow < 10000) {
                        int copyRow = 3;
                        myOffice.Excel.Range row = sh.Rows[startRow];

                        ProjectRow selRow = new ProjectRow(row);
                        DateTime sDate = new DateTime(1900, 1, 1);
                        startRow++;

                        if (Tools.Nz(selRow.RowType, "").ToUpper() == "DB") {
                            break;
                        }

                        if (selRow.JobDatum == sDate | !Tools.SheetsExists(jeWb, selRow.Durchführender1)) {
                            continue;
                        }

                        if (selRow.IsReadyForJobevidenz) {
                            jeSheet = jeWb.Sheets[selRow.Durchführender1];
                            selRow.RowID = DateTime.Now.ToString("yyMMddhhmmss") + DateTime.Now.Millisecond.ToString();
                            copyRow = GetFirstFreeRowNumber(startCopyRow, jeSheet);
                            ((Microsoft.Office.Interop.Excel._Worksheet)sh).Activate();

                            CopyProduktZeile(row, jeSheet, copyRow, false, false, true, wb.FullName);
                            //row.Font.Name = Extensions.tpFontBold;
                            Extensions.SetFontBoldOrNormal(row, Extensions.tpFont.Bold);
                            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Je: " + sheet.Kundenname + sheet.Projekt + ", " + sh.Name + ", " + selRow.Durchführender1 + ", " + selRow.RowText + " (" + selRow.JobDatum.ToString(Extensions.tpDateFormat) + ")", false);
                            exported = true;

                            if (selRow.Durchführender2 != "") {
                                if (!Tools.SheetsExists(jeWb, selRow.Durchführender2)) {
                                    continue;
                                }
                                jeSheet = jeWb.Sheets[selRow.Durchführender2];
                                copyRow = GetFirstFreeRowNumber(copyRow, jeSheet);
                                ((Microsoft.Office.Interop.Excel._Worksheet)sh).Activate();
                                CopyProduktZeile(row, jeSheet, startCopyRow, true, false, true, wb.FullName);
                                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Je: " + sheet.Kundenname + sheet.Projekt + ", " + sh.Name + ", " + selRow.Durchführender2 + ", " + selRow.RowText + " (" + selRow.JobDatum.ToString(Extensions.tpDateFormat) + ")", false);
                                exported = true;
                            }
                            selRow.SetExported("Je");
                        }

                    }
                    if (exported) {
                        wb.Save();
                        string msg1 = String.Format(Resources.ExportOk, "(Jobevidenz)");
                        MessageBox.Show(msg1, wb.Name, MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                    }
                } catch (Exception ex) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "ExportJobevidenz: (" + wb.Name + "): " + ex.Message, true);
                } finally {
                    if (jeWb != null) {
                        jeWb.Close(true);
                    }
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "ExportJobevidenz: " + wb.Name + "(" + sh.Name + ")", false);
                }
            }
        }

        public static void ExportLeistungen(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {
            ExportLeistungen(exApp, wb, sh, true);
        }

        public static void ExportLeistungen(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, bool showMessage) {
            ProjectSheet sheet = new ProjectSheet(sh, false);
            if (sheet.IsCalcSheet) {
                Workbook leWb = null;
                Worksheet leSheet = null;
                StringBuilder err = new StringBuilder();

                #region Check File exists

                if (!File.Exists(Settings.Default.DateiLesu)) {
                    string msg = String.Format(Resources.DateiIstNichtVorhanden, Settings.Default.DateiLesu);
                    MessageBox.Show(msg, wb.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                #endregion

                #region Check File is in use

                if (Tools.FileIsInUse(Settings.Default.DateiLesu)) {
                    string msg = String.Format(Resources.DateiIstBereitsGeöffnet, Settings.Default.DateiLesu);
                    MessageBox.Show(msg, wb.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                #endregion

                leWb = exApp.Workbooks.Open(Settings.Default.DateiLesu);
                int startRow = Settings.Default.ZeileStart;
                if (sheet.SheetName.ToUpper() == "BVKA") {
                    startRow = 4;
                }
                int startCopyRow = 3;
                try {
                    bool startRowSelect = false;
                    while (startRowSelect == false & startRow < 10000) {
                        int copyRow = 3;
                        myOffice.Excel.Range row = sh.Rows[startRow];
                        ProjectRow selRow = new ProjectRow(row);
                        DateTime sDate = new DateTime(1900, 1, 1);
                        bool fehler = false;

                        #region Abbruchbedingungen

                        if (Tools.Nz(selRow.RowType, "").ToUpper() == "DB") {
                            break;
                        }

                        if (selRow.IsSummenZeile) {
                            startRow++;
                            continue;
                        }

                        if (!selRow.LeistungExported && selRow.Fahrzeug != "S") {
                            if (selRow.Fahrzeug == "Err") {
                                err.AppendLine("Zeile : " + startRow.ToString() + " / Fahrzeug nicht vorhanden!");
                                fehler = true;
                            }

                            if (selRow.Fahrzeug == "" && (selRow.Durchführender1 != "" | selRow.JobDatum.Date != sDate.Date)) {
                                err.AppendLine("Zeile : " + startRow.ToString() + " / Fahrzeug ist leer!");
                                fehler = true;
                            }

                            if (selRow.Durchführender1 == "" && ((selRow.Fahrzeug != "" & selRow.Fahrzeug != "S") | selRow.JobDatum.Date != sDate.Date)) {
                                err.AppendLine("Zeile : " + startRow.ToString() + " / Durchführender 1 ist leer!");
                                fehler = true;
                            }

                            if (selRow.JobDatum.Date == sDate.Date && ((selRow.Fahrzeug != "" & selRow.Fahrzeug != "S") | selRow.Durchführender1 != "")) {
                                err.AppendLine("Zeile : " + startRow.ToString() + " / Jobdatum fehlt!");
                                fehler = true;
                            }

                            if (selRow.Durchführender1 == "Err") {
                                err.AppendLine("Zeile : " + startRow.ToString() + " / Durchführender 1 nicht vorhanden!");
                                fehler = true;
                            }

                            if (selRow.Durchführender2 == "Err") {
                                err.AppendLine("Zeile : " + startRow.ToString() + " / Durchführender 2 nicht vorhanden!");
                                fehler = true;
                            }

                            if (selRow.IsReadyForExport) {
                                if (!selRow.IsPauschalTime) {
                                    if (selRow.JobTime == 0.0) {
                                        err.AppendLine("Zeile : " + startRow.ToString() + " / Keine Anfangszeit angegeben!");
                                        fehler = true;
                                    }

                                    if (selRow.JobTime != 0.0 && (selRow.JobEndTime <= selRow.JobTime)) {
                                        err.AppendLine("Zeile : " + startRow.ToString() + " / Job-Endzeit ist falsch!");
                                        fehler = true;
                                    }
                                }
                            }

                            if (fehler) {
                                startRow++;
                                continue;
                            }

                        }

                        if ((selRow.JobDatum.Date == sDate.Date) | (!Tools.SheetsExists(leWb, selRow.Durchführender1)) | (!selRow.IsLeOrLa)) {
                            startRow++;
                            continue;
                        }

                        #endregion

                        if (selRow.IsReadyForExport) {
                            bool stellVertreter = false;

                            if (selRow.Stellvertreter != "") {
                                leSheet = leWb.Sheets[selRow.Stellvertreter];
                                stellVertreter = true;
                            } else {
                                leSheet = leWb.Sheets[selRow.Durchführender1];
                            }
                            copyRow = GetFirstFreeRowNumber(startCopyRow, leSheet);
                            ((Microsoft.Office.Interop.Excel._Worksheet)sh).Activate();
                            CopyProduktZeile(row, leSheet, copyRow, false, stellVertreter, false, "");
                            if (selRow.IsReadyForExportExpense) {
                                ExportDiäten(exApp, wb, sh, row, selRow.Durchführender1);
                            }
                            //row.Font.Name = Extensions.tpFontBold;
                            Extensions.SetFontBoldOrNormal(row, Extensions.tpFont.Regular);
                            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Le: " + sheet.Kundenname + sheet.Projekt + ", " + sh.Name + ", " + selRow.Durchführender1 + ", " + selRow.RowText + " (" + selRow.JobDatum.ToString(Extensions.tpDateFormat) + ")", false);
                            err.AppendLine("Zeile: " + startRow.ToString() + " wurde exportiert.");
                            selRow.Stellvertreter = "";
                            if (selRow.Durchführender2 != "") {
                                if (!Tools.SheetsExists(leWb, selRow.Durchführender2)) {
                                    continue;
                                }
                                leSheet = leWb.Sheets[selRow.Durchführender2];
                                copyRow = GetFirstFreeRowNumber(startCopyRow, leSheet);
                                ((Microsoft.Office.Interop.Excel._Worksheet)sh).Activate();
                                CopyProduktZeile(row, leSheet, copyRow, true);
                                if (selRow.IsReadyForExportExpense) {
                                    ExportDiäten(exApp, wb, sh, row, selRow.Durchführender2);
                                } LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Le: " + sheet.Kundenname + sheet.Projekt + ", " + sh.Name + ", " + selRow.Durchführender2 + ", " + selRow.RowText + " (" + selRow.JobDatum.ToString(Extensions.tpDateFormat) + ")", false);
                            }
                            if (selRow.IsReadyToStorno) {
                                row.Delete(Shift: XlDeleteShiftDirection.xlShiftUp);
                            } else {
                                selRow.SetExported("Le");
                                startRow++;
                            }
                        } else {
                            startRow++;
                        }

                    }
                    if (err.Length > 0) {
                        MessageBox.Show(err.ToString(), wb.Name + " (Leistungen)", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }

                    //if (exported && showMessage & err.Length == 0) {
                    //    string msg1 = String.Format(Resources.ExportOk, "(Leistungen)");
                    //    MessageBox.Show(msg1, wb.Name, MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                    //}

                } catch (Exception ex) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "ExportLeistungen: " + ex.Message, true);
                } finally {
                    if (leWb != null) {
                        leWb.Close(true);
                    }
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "ExportLeistungen: " + wb.Name + "(" + sh.Name + ")", false);
                }


            }
        }

        #endregion

        #region Export + Funktion + Dokument

        public static void DokumentAngebot(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {
            bool wordStarted = Office.WordIsStarted();
            bool lineBreak = false;
            myOffice.Word.Application appWord = null;
            myOffice.Excel.Range row1 = sh.Cells[1, "C"];

            string abrName = "";
            string currentDocName = "";
            if (row1.Value != null) {
                abrName = Convert.ToString(row1.Value);
            }

            try {
                ProjectSheet sheet = new ProjectSheet(sh, false);
                if (sheet.IsCalcSheet) {
                    return;
                }

                if (abrName.Length == 0) {
                    string calcProject = "";
                    foreach (Worksheet s1 in wb.Sheets) {
                        ProjectSheet ps1 = new ProjectSheet(s1, false);
                        if (ps1.IsCalcSheet) {
                            calcProject = ps1.SheetName;
                        }
                    }

                    if (calcProject.Length == 0) {
                        abrName = "P" + sheet.ProjektNummer.ToString() + "k1";
                    } else {
                        abrName = calcProject;
                    }

                    if (!Tools.SheetsExists(wb, abrName)) {
                        string msg = "Register " + abrName + " wurde nicht gefunden!";
                        MessageBox.Show(msg, sh.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                }

                Worksheet abrSheet = wb.Sheets[abrName];
                ProjectSheet abr = new ProjectSheet(abrSheet, false);

                Angebot anWindow = null;
                try {
                    anWindow = new Angebot(sheet.Kundenname, sheet.Suchbegriff, Extensions.tpWindowType.Angebot);
                    anWindow.ShowDialog();
                } catch (Exception ex) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "Angebot.ShowDialog: " + ex.Message, true);
                }

                if (anWindow.Cancel) {
                    return;
                }

                if ((sheet.SheetType == (int)Extensions.SheetType.Angebot) | sheet.SheetType == 0) {
                    appWord = TpTools.Office.GetWordApplication();
                    if (appWord == null) {
                        string msg = "Es konnte Word nicht gestartet werden. Bitte Word komplett schließen (Prozess) und neu öffnen.";
                        LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "DokumentAngebot: " + msg, true);
                        MessageBox.Show(msg, "Dokument - Angebot", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        return;
                    }
                    string fileName = CreateFileName(sheet.Kundenname, sheet.Projekt, Extensions.DocType.Angebot, Customer.Verkauf, Extensions.DocExt.DocX, "p" + sheet.ProjektNummer.ToString());
                    myOffice.Word.Document mDoc = null;
                    int repeats = 0;
                    while (mDoc == null & repeats < 3) {
                        switch (anWindow.Versand) {
                            case (int)GlobalVar.VersandArt.Mail:
                                mDoc = GetWordDocument(appWord, fileName, Settings.Default.VorlageWordAngebotMail);
                                break;
                            case (int)GlobalVar.VersandArt.Brief:
                                mDoc = GetWordDocument(appWord, fileName, Settings.Default.VorlageWordAngebotBrief);
                                lineBreak = true;
                                break;
                            case (int)GlobalVar.VersandArt.Fax:
                                mDoc = GetWordDocument(appWord, fileName, Settings.Default.VorlageWordAngebotFax);
                                break;
                        }
                        repeats++;
                    }
                    if (mDoc == null) {
                        LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "DokumentAngebot: mDoc = null (" + fileName + ")", true);
                    }

                    if (Office.ExistingDocumentProperty(mDoc, "Subject")) {
                        mDoc.CustomDocumentProperties["Subject"].Delete();
                    }
                    mDoc.CustomDocumentProperties.Add("Subject", false, Microsoft.Office.Core.MsoDocProperties.msoPropertyTypeString, anWindow.MyRecipient.Betreff);

                    if (Office.ExistingDocumentProperty(mDoc, "TpDocType")) {
                        mDoc.CustomDocumentProperties["TpDocType"].Delete();
                    }
                    mDoc.CustomDocumentProperties.Add("TpDocType", false, Microsoft.Office.Core.MsoDocProperties.msoPropertyTypeNumber, Extensions.DocType.Angebot);
                    currentDocName = fileName;

                    mDoc.SaveAs2(FileName: fileName);
                    Office.FillWordDocumentFields(appWord, mDoc, Extensions.DocType.Angebot, anWindow.SelectedType, anWindow.MyRecipient, null, null, anWindow.Beilagen, anWindow.MailCC, lineBreak);
                    myOffice.Excel.Range r1 = sh.Range[sh.PageSetup.PrintArea];
                    r1.Select();
                    r1.CopyPicture(myOffice.Excel.XlPictureAppearance.xlPrinter, myOffice.Excel.XlCopyPictureFormat.xlPicture);
                    foreach (myOffice.Word.Bookmark bm in mDoc.Bookmarks) {
                        if (bm.Name == "Angebot") {
                            bm.Select();
                            appWord.Selection.Paste();
                        }
                    }

                    abr.WriteJob("Angebot", "", DateTime.Now, "Lf", Environment.UserName, 0, fileName, Path.GetFileName(fileName));

                    appWord.Visible = true;
                    appWord.Activate();
                }
            } catch (Exception e) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, String.Format(Resources.DokumentAngebotErrorMsg, e.Message), true);
                string msg = "Es ist ein Fehler während des Exports in das Dokument aufgetreten.";
                MessageBox.Show(msg, sh.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                msg = "User: " + System.Environment.UserName.FirstCharBig() + "\n\r" + "Zeit : " + DateTime.Now.ToString(Extensions.tpDateFormat + " " + Extensions.tpTimeFormat) + "\n\r" + e.Message;
                Tools.SendMail(Settings.Default.ErrorMailFrom, Settings.Default.ErrorMailTo, "DokumentAngebot - Fehler", msg, null);
                if (appWord != null & !wordStarted) {
                    ((Microsoft.Office.Interop.Word._Application)appWord).Quit(true);
                }
            } finally {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "DokumentAngebot: " + wb.Name + "(" + sh.Name + "/" + currentDocName + ")", false);
            }
        }

        public static void DokumentAuftragsBestaetigung(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {
            bool wordStarted = Office.WordIsStarted();
            bool lineBreak = false;
            myOffice.Word.Application appWord = null;
            string fileName = "";
            try {
                ProjectSheet sheet = new ProjectSheet(sh, false);
                if (sheet.IsCalcSheet) {
                    return;
                }

                string abrName = "P" + sheet.ProjektNummer + "r";
                if (!Tools.SheetsExists(wb, abrName)) {
                    string msg = "Abrechnung " + abrName + " wurde nicht gefunden!";
                    MessageBox.Show(msg, sh.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                Worksheet abrSheet = wb.Sheets[abrName];
                ProjectSheet abr = new ProjectSheet(abrSheet, false);

                Angebot anWindow = new Angebot(sheet.Kundenname, sheet.Suchbegriff, Extensions.tpWindowType.Auftragsbestätigung);
                anWindow.ShowDialog();

                if (anWindow.Cancel) {
                    return;
                }

                if ((sheet.SheetType == (int)Extensions.SheetType.Auftragsbestaetigung) | sheet.SheetType == 0) {
                    appWord = TpTools.Office.GetWordApplication();
                    if (appWord == null) {
                        MessageBox.Show("Es konnte Word nicht gestartet werden. Bitte Word komplett schließen (Prozess) und neu öffnen.", "Dokument - Auftragsbestätigung", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        return;
                    }
                    fileName = CreateFileName(sheet.Kundenname, sheet.Projekt, Extensions.DocType.Auftragsbestaetigung, Customer.Kunde, Extensions.DocExt.DocX, "p" + sheet.ProjektNummer.ToString());
                    myOffice.Word.Document mDoc = null;
                    int repeats = 0;
                    while (mDoc == null & repeats < 3) {
                        switch (anWindow.Versand) {
                            case (int)GlobalVar.VersandArt.Mail:
                                mDoc = GetWordDocument(appWord, fileName, Settings.Default.VorlageWordAB_Mail);
                                break;
                            case (int)GlobalVar.VersandArt.Brief:
                                mDoc = GetWordDocument(appWord, fileName, Settings.Default.VorlageWordAB_Brief);
                                lineBreak = true;
                                break;
                            case (int)GlobalVar.VersandArt.Fax:
                                mDoc = GetWordDocument(appWord, fileName, Settings.Default.VorlageWordAB_Fax);
                                break;
                        }
                        repeats++;
                    }
                    if (mDoc == null) {
                        LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "DokumentAuftragsbestätigung: mDoc = null (" + fileName + ")", true);
                    }

                    if (Office.ExistingDocumentProperty(mDoc, "Subject")) {
                        mDoc.CustomDocumentProperties["Subject"].Delete();
                    }
                    mDoc.CustomDocumentProperties.Add("Subject", false, Microsoft.Office.Core.MsoDocProperties.msoPropertyTypeString, anWindow.MyRecipient.Betreff);
                    if (Office.ExistingDocumentProperty(mDoc, "TpDocType")) {
                        mDoc.CustomDocumentProperties["TpDocType"].Delete();
                    }
                    mDoc.CustomDocumentProperties.Add("TpDocType", false, Microsoft.Office.Core.MsoDocProperties.msoPropertyTypeNumber, Extensions.DocType.Auftragsbestaetigung);


                    mDoc.SaveAs2(FileName: fileName);
                    Office.FillWordDocumentFields(appWord, mDoc, Extensions.DocType.Auftragsbestaetigung, anWindow.SelectedType, anWindow.MyRecipient, null, null, anWindow.Beilagen, anWindow.MailCC, lineBreak);
                    myOffice.Excel.Range r1 = sh.Range[sh.PageSetup.PrintArea];
                    r1.Select();
                    r1.CopyPicture(myOffice.Excel.XlPictureAppearance.xlPrinter, myOffice.Excel.XlCopyPictureFormat.xlPicture);
                    foreach (myOffice.Word.Bookmark bm in mDoc.Bookmarks) {
                        if (bm.Name == "Angebot") {
                            bm.Select();
                            appWord.Selection.Paste();
                        }
                    }

                    abr.WriteJob("Auftragsbestätigung", "Verkauf", DateTime.Now, "Lf", Environment.UserName, 0, fileName, Path.GetFileName(fileName));

                    appWord.Visible = true;
                    appWord.Activate();
                }
            } catch (Exception e) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "DokumentAuftragsbestätigung: (" + fileName + "): " + e.Message, true);
                string msg = "Es ist ein Fehler während des Exports in die Auftragsbestätigung aufgetreten.";
                MessageBox.Show(msg, sh.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                msg = "User: " + System.Environment.UserName.FirstCharBig() + "\n\r" + "Zeit : " + DateTime.Now.ToString(Extensions.tpDateFormat + " " + Extensions.tpTimeFormat) + "\n\r" + e.Message;
                Tools.SendMail(Settings.Default.ErrorMailFrom, Settings.Default.ErrorMailTo, "DokumentAuftragsbestätigung - Fehler", msg, null);
                if (appWord != null & !wordStarted) {
                    ((Microsoft.Office.Interop.Word._Application)appWord).Quit(true);
                }
            } finally {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "DokumentAuftragsbestätigung: " + wb.Name + "(" + sh.Name + "/" + fileName + ")", false);
            }
        }

        public static void DokumentRechnung(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {
            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Start DokumentRechnung", false);
            bool wordStarted = Office.WordIsStarted();
            bool lineBreak = false;
            myOffice.Word.Application appWord = null;
            int currentInvoiceNumber = 0;
            string currentFileName = "";

            try {

                appWord = TpTools.Office.GetWordApplication();
                if (appWord == null) {
                    MessageBox.Show("Word-Vorlage konnte nicht geöffnet werden. Bitte ein Word-Document öffnen.", "Dokument - Rechnung", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    return;
                }

                ProjectSheet inv = new ProjectSheet(sh, false);
                string abrName = "P" + inv.ProjektNummer + "r";
                if (!Tools.SheetsExists(wb, abrName)) {
                    string msg = "Abrechnung " + abrName + " wurde nicht gefunden!";
                    MessageBox.Show(msg, sh.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                Worksheet abrSheet = wb.Sheets[abrName];
                ProjectSheet abr = new ProjectSheet(abrSheet, true);

                if (inv.SheetType != (int)Extensions.SheetType.Rechnung) {
                    return;
                }

                Organisation org = OrganisationDataManager.LoadOrganisation(inv.Kundenname, inv.Suchbegriff);
                if (!CheckOrgData(org, inv.Suchbegriff, wb.Name)) {
                    return;
                }

                if (GlobalVar.currentOrganisation != null) {
                    if (org.OrgKey != GlobalVar.currentOrganisation.OrgKey) {
                        GlobalVar.currentOrganisation = org;
                    }
                } else {
                    GlobalVar.currentOrganisation = org;
                }

                #region Get MyModel

                InvoiceObject i = GetNewInvoiceWithData(inv, abr, org, 0, exApp);
                i.MyPersons = org.MyPersons;
                i.ProjektBezeichnung = inv.Projekt;
                i.IsFirstInvoice = true;

                //LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "IsFirstInvoice: " + i.IsFirstInvoice.ToString(), false);

                if (abr.InvoiceList.Count > 0) {
                    i.IsFirstInvoice = false;
                }

                if (i.Ust != 0) {
                    i.SachKonto = "400 00";
                } else {
                    i.SachKonto = "400 30";
                }

                #endregion

                #region Window Rechnungsdaten

                Rechnung re;
                if (GlobalVar.currentInvoice != null) {
                    if (org.OrgKey == GlobalVar.currentOrganisation.OrgKey & GlobalVar.currentInvoice.ProjektBezeichnung == i.ProjektBezeichnung) {
                        i.UID = (GlobalVar.currentInvoice.UID != null ? GlobalVar.currentInvoice.UID : i.UID);
                        i.ReEmpfaenger = (GlobalVar.currentInvoice.ReEmpfaenger != null ? GlobalVar.currentInvoice.ReEmpfaenger : i.ReEmpfaenger);
                        i.RePerson = (GlobalVar.currentInvoice.RePerson != null ? GlobalVar.currentInvoice.RePerson : i.RePerson);
                        i.ReStrasse = (GlobalVar.currentInvoice.ReStrasse != null ? GlobalVar.currentInvoice.ReStrasse : i.ReStrasse);
                        i.RePlzOrt = (GlobalVar.currentInvoice.RePlzOrt != null ? GlobalVar.currentInvoice.RePlzOrt : i.RePlzOrt);
                        i.ReEmpfaenger = (GlobalVar.currentInvoice.ReEmpfaenger != null ? GlobalVar.currentInvoice.ReEmpfaenger : i.ReEmpfaenger);
                        i.ReBetreff = (GlobalVar.currentInvoice.ReBetreff != null ? GlobalVar.currentInvoice.ReBetreff : i.ReBetreff);
                        i.ReEmpfaengerMail = (GlobalVar.currentInvoice.ReEmpfaengerMail != null ? GlobalVar.currentInvoice.ReEmpfaengerMail : i.ReEmpfaengerMail);
                        i.ZahlArt = (GlobalVar.currentInvoice.ZahlArt != TpBaseObjects.Global.InvoicePayType.BarNachMontage ? GlobalVar.currentInvoice.ZahlArt : i.ZahlArt);
                        i.RechnungVersand = (GlobalVar.currentInvoice.RechnungVersand != TpBaseObjects.Global.InvoiceSendType.Persoenlich ? GlobalVar.currentInvoice.RechnungVersand : i.RechnungVersand);
                        i.Teilrechnung = (GlobalVar.currentInvoice.Teilrechnung != false ? GlobalVar.currentInvoice.Teilrechnung : i.Teilrechnung);
                        if (GlobalVar.currentInvoice.Beilagen != null) {
                            i.Beilagen = GlobalVar.currentInvoice.Beilagen;
                        }
                        re = new Rechnung(i);
                    } else {
                        re = new Rechnung(i);
                    }
                } else {
                    Beilage bel = new Beilage();
                    bel.BeilagenText = "Zahlschein";
                    bel.IsChecked = false;
                    if (i.Beilagen == null) {
                        i.Beilagen = new List<Beilage>();
                    }
                    i.Beilagen.Add(bel);
                    re = new Rechnung(i);
                }

                re.ShowDialog();

                if (re.Cancel) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "DokumentRechnung: ShowDialog.Cancel", false);
                    GlobalVar.currentInvoice = re.MyModel;
                    return;
                }

                #endregion

                bool useOldRow = i.RechnungsNummer != 0;

                if (!GlobalVar.DebugMode) {
                    i.RechnungsNummer = GetRechnungsNummer(i, inv.Projekt, exApp);
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "GetRechnungsNummer: " + i.RechnungsNummer.ToString(), false);
                    if (i.RechnungsNummer <= 0) {
                        LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "DokumentRechnung: GetRechnungsNummer(0)", true);
                        return;
                    }
                } else {
                    i.RechnungsNummer = 999;
                }

                currentInvoiceNumber = i.RechnungsNummer;

                re.MyRecipient.Betreff = "Rechnung";

                if (i.SummeBrutto < 0) {
                    re.MyRecipient.Betreff = "Gutschrift";
                }

                Write2Liqui(i, useOldRow, exApp);


                string fileName = "";
                if (!GlobalVar.DebugMode) {
                    fileName = CreateFileName(inv.Kundenname, inv.Projekt, Extensions.DocType.Rechnung, Customer.Rewe, Extensions.DocExt.DocX, "p" + inv.ProjektNummer.ToString(), i.RechnungsNummer, i.Rechnungsdatum.Year.ToString().Right(2));
                } else {
                    fileName = @"T:\Rewe\TestRechnung.docx";
                }

                abr.WriteJob("Abrechnung", "Kunde", DateTime.Now, "Lf", i.Ma, i.RechnungsNummer, fileName, i.Rechnungsdatum.Year.ToString().Right(2) + i.RechnungsNummer.ToString("000") + "." + Extensions.DocExt.DocX);
                abr.WriteJob("Abrechnung Reparatur", "Kunde", DateTime.Now, "Lf", i.Ma, i.RechnungsNummer, fileName, i.Rechnungsdatum.Year.ToString().Right(2) + i.RechnungsNummer.ToString("000") + "." + Extensions.DocExt.DocX);

                if (i.Teilrechnung) {
                    abr.CopyJob("Abrechnung", "Kunde");
                    abr.CopyJob("Abrechnung Reparatur", "Kunde");
                }

                if (!GlobalVar.DebugMode) {
                    ExportLeistungen(exApp, wb, abr.MySheet, false);
                }

                inv.ActivateSheet();

                myOffice.Word.Document mDoc = null;
                int repeats = 0;
                while (mDoc == null & repeats < 3) {
                    switch (i.RechnungVersand) {
                        case TpBaseObjects.Global.InvoiceSendType.Persoenlich:
                            mDoc = GetWordDocument(appWord, fileName, Settings.Default.VorlageWordRechnungBriefPapier);
                            break;
                        case TpBaseObjects.Global.InvoiceSendType.Mail:
                            mDoc = GetWordDocument(appWord, fileName, Settings.Default.VorlageWordRechnungMail);
                            break;
                        case TpBaseObjects.Global.InvoiceSendType.Post:
                            mDoc = GetWordDocument(appWord, fileName, Settings.Default.VorlageWordRechnungBriefPapier);
                            lineBreak = true;
                            break;
                        case TpBaseObjects.Global.InvoiceSendType.Fax:
                            mDoc = GetWordDocument(appWord, fileName, Settings.Default.VorlageWordRechnungMail);
                            break;
                    }
                    repeats++;
                }
                if (mDoc == null) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "DokumentRechnung: mDoc = null (" + fileName + ")", true);
                }


                if (File.Exists(fileName)) {
                    File.Delete(fileName);
                }

                mDoc.SaveAs2(FileName: fileName);

                if (Office.ExistingDocumentProperty(mDoc, "Subject")) {
                    mDoc.CustomDocumentProperties["Subject"].Delete();
                }
                mDoc.CustomDocumentProperties.Add("Subject", false, Microsoft.Office.Core.MsoDocProperties.msoPropertyTypeString, i.Betreff);

                Office.FillWordDocumentFields(appWord, mDoc, Extensions.DocType.Rechnung, re.SelectedType, re.MyRecipient, i, null, re.Beilagen, null, lineBreak);
                mDoc.Save();
                myOffice.Excel.Range r1 = sh.Range[sh.PageSetup.PrintArea];
                r1.Select();
                r1.CopyPicture(myOffice.Excel.XlPictureAppearance.xlPrinter, myOffice.Excel.XlCopyPictureFormat.xlPicture);
                foreach (myOffice.Word.Bookmark bm in mDoc.Bookmarks) {
                    if (bm.Name == "Rechnung") {
                        bm.Select();
                        appWord.Selection.Paste();
                    }
                }
                mDoc.Save();
                appWord.Visible = true;
                appWord.Activate();
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, String.Format(Resources.DokumentRechnungErrorMsg, ex.Message), true);
                string msg = "DokumentRechnung: " + ex.Message;
                MessageBox.Show(msg, sh.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                msg = "User: " + System.Environment.UserName.FirstCharBig() + "\n\r" + "Zeit : " + DateTime.Now.ToString(Extensions.tpDateFormat + " " + Extensions.tpTimeFormat) + "\n\r" + ex.Message;
                Tools.SendMail(Settings.Default.ErrorMailFrom, Settings.Default.ErrorMailTo, "DokumentAngebot - Fehler", msg, null);
                if (appWord != null & !wordStarted) {
                    //appWord.Quit();
                }
            } finally {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Dokumentrechnung: " + wb.Name + "(" + sh.Name + ", " + currentFileName + ")", false);
            }

        }

        public static void DokumentMahnung(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {
            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Start DokumentMahnung", false);
            double currentAmount = 0.0;

            try {
                int invoice = 0;
                ProjectSheet ms = new ProjectSheet(sh, true);

                foreach (Worksheet s1 in wb.Sheets) {
                    SheetObj so = new SheetObj(s1);
                    if (so.SheetType == Extensions.SheetType.Rechnung && ms.ProjektNummer == so.ProjektNummer) {
                        if (ms.InvoiceList.Count > 0) {
                            ms.InvoiceList[invoice].Amount = so.SummeBrutto;
                            invoice++;
                        }
                    }
                }

                if (ms.InvoiceList.Count == 0) {
                    MessageBox.Show("Es sind keine Rechnungen für eine Mahnung vorhanden.", "Dokument Mahnung", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    return;
                }

                Angebot mahn = new Angebot(ms.Kundenname, ms.Suchbegriff, Extensions.tpWindowType.Mahnung);
                mahn.SetInvoicesDatasource(ms.InvoiceList);
                mahn.SetReminderDatasource(ms.Mahnungen);
                mahn.SetPaymentDatasource(ms.Zahlungen);
                mahn.SetAkontoDatasource(ms.AkontoList);
                mahn.ShowDialog();

                if (mahn.Cancel) {
                    return;
                }

                myOffice.Outlook.Application olApp = Office.GetOutlookApplication();
                myOffice.Outlook.MailItem msg = olApp.CreateItem(myOffice.Outlook.OlItemType.olMailItem);

                msg.BodyFormat = myOffice.Outlook.OlBodyFormat.olFormatHTML;

                msg.To = mahn.MyRecipient.EMail;

                StringBuilder anCC = new StringBuilder();
                foreach (Person pers in mahn.MailCC) {
                    if (pers.SendToThisPerson) {
                        if (anCC.Length != 0) {
                            anCC.Append(";");
                        }
                        anCC.Append(pers.EMail);
                    }
                }
                msg.CC = anCC.ToString();

                msg.Subject = "Offene Forderung";

                double sumInv = 0.0;
                double sumZa = 0.0;
                StringBuilder body = new StringBuilder();
                body.Append("<!DOCTYPE html><html lang='en' xmlns='http://www.w3.org/1999/xhtml'><head><meta charset='utf-8' /><title></title><style type='ext/css'>.tp1 {font-family: Arial, Helvetica, Sans-Serif;font-size: 13px;}</style></head><body>");
                body.Append("<p class='tp1'>Guten Tag " + mahn.MyRecipient.Anrede + "!</p>");
                if (ms.Mahnungen.Count == 0) {
                    body.Append("<p class='tp1'>Nachstehende Rechnungen sind bis heute nicht vollständig bezahlt:</p>");
                    body.Append("<table><tr><td class='tp1' style='width:100px'>Rechnung</td><td class='tp1' style='width:100px'>Datum</td><td class='tp1' style='width:100px; text-align:right'>EUR</td></tr>");


                    foreach (InvoiceObj inv in ms.InvoiceList) {
                        sumInv += inv.Amount;
                        body.Append("<tr><td class='tp1'>" + inv.Date.Year.ToString().Right(2) + inv.Number.ToString("000") + "</td><td class='tp1'>" + inv.Date.ToString(Extensions.tpDateFormatExtern) + "</td><td class='tp1' style='width:100px; text-align:right'>" + String.Format("{0:n}", inv.Amount) + "</td></tr>");
                    }
                    body.Append("<strong><tr><td class='tp1'>Summe</td><td>&nbsp;</td><td class='tp1' style='width:100px; text-align:right'>" + String.Format("{0:n}", sumInv) + "</td></tr></strong></table>");

                    if (ms.Zahlungen.Count > 0) {
                        body.Append("<p class='tp1'>Bisher sind folgende Zahlungen erfolgt:</p>");
                        body.Append("<table><tr><td class='tp1' style='width:100px'>Datum</td><td class='tp1' style='width:100px; text-align:right'>EUR</td></tr>");
                        foreach (AkontoObj za in ms.Zahlungen) {
                            sumZa += za.Amount;
                            body.Append("<tr><td class='tp1'>" + za.Date.ToString(Extensions.tpDateFormatExtern) + "</td><td class='tp1' style='width:100px; text-align:right'>" + String.Format("{0:n}", za.Amount) + "</td></tr>");
                        }
                        body.Append("<strong><tr><td class='tp1'>Summe</td><td class='tp1' style='width:100px; text-align:right'>" + String.Format("{0:n}", sumInv) + "</td></tr></strong></table>");
                    } else {
                        body.Append("<p class='tp1'>Bisher sind keine Zahlungen erfolgt</p>");
                    }
                    //body.Append("<p>&nbsp;</p>");
                    body.Append("<table><strong><tr><td class='tp1' style='width:200px'>Überfalliger Saldo</td><td class='tp1' style='width:100px; text-align:right'>" + String.Format("{0:n}", (sumInv - sumZa)) + "</td></tr></strong></table>");
                    body.Append("<p class='tp1'>Wir ersuchen um prompte Überweisung.</p>");
                } else {
                    double dblPayment = 0.0;
                    int anzPayment = 0;
                    foreach (InvoiceObj inv in ms.InvoiceList) {
                        sumInv += inv.Amount;
                    }
                    foreach (AkontoObj za in ms.Zahlungen) {
                        sumZa += za.Amount;
                    }
                    body.Append("<p class='tp1'>Seit unserer Mahnung " + ms.MaxMahnungsDatum.ToString(Extensions.tpDateFormatExtern) + " haben wir ");
                    if (ms.maxZahlungsDatum > ms.MaxMahnungsDatum) {
                        foreach (AkontoObj za in ms.Zahlungen) {
                            if (za.Date == ms.maxZahlungsDatum) {
                                dblPayment += za.Amount;
                                anzPayment++;
                            }
                        }
                    }
                    if (dblPayment != 0.0) {
                        body.Append(anzPayment.ToString() + " Zahlung" + (anzPayment > 1 ? "en" : "") + "erhalten.</p>");
                    } else {
                        body.Append("keine Zahlung erhalten.</p>");
                    }
                    body.Append("<p class='tp1'>Die Zahlung von EUR " + String.Format("{0:n}", (sumInv - sumZa)) + " ist überfällig. Falls bis " + Tools.NextGoodMonday(DateTime.Today).ToString(Extensions.tpDateFormatExtern) + " nicht ein Zahlungseingang über diesen Betrag bei uns erfolgt ist, werden wir ohne weitere Korrespondenz eine Mahnklage veranlassen.</p>");
                }

                currentAmount = (sumInv - sumZa);
                body.Append("</body></html>");
                body.Append(ReadSignature(Environment.UserName));
                msg.HTMLBody = body.ToString();
                msg.Display(false);
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "DokumentMahnung: " + ex.Message, true);
            } finally {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "DokumentMahnung: " + wb.Name + "(" + sh.Name + ", EUR " + String.Format("{0:n}", currentAmount) + ")", false);
            }

        }

        #endregion

        #region Import Ka
        #endregion

        #region Bearbeitung Ka
        #endregion

        #endregion

        #region Workbook Events

        public static void WBActivate(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {
            SetKontextMenu(exApp, wb, sh, false);
        }

        public static void WBAfterSave(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {
            if (exApp.DefaultSaveFormat != XlFileFormat.xlOpenXMLWorkbook) {
                exApp.DefaultSaveFormat = XlFileFormat.xlOpenXMLWorkbook;
            }
        }

        public static bool WbBeforeClose(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, bool cancel) {

            if (!cancel) {
                if (exApp.DefaultSaveFormat != XlFileFormat.xlOpenXMLWorkbook) {
                    exApp.DefaultSaveFormat = XlFileFormat.xlOpenXMLWorkbook;
                }
            }

            return cancel;
        }

        public static bool WBBeforeSave(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, bool cancel) {
            if (wb.FileFormat != XlFileFormat.xlOpenXMLTemplateMacroEnabled) {
                ProjectSheet sheet = new ProjectSheet(sh, false);
                if (sheet.IsCalcSheet & !sheet.InternesProjekt) {
                    cancel = !CheckCalcSheets(wb, sh);
                }
            }
            if (exApp.DefaultSaveFormat != XlFileFormat.xlOpenXMLWorkbookMacroEnabled) {
                exApp.DefaultSaveFormat = XlFileFormat.xlOpenXMLWorkbookMacroEnabled;
            }
            return cancel;
        }

        public static void WBDeactivate(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {
            DeleteMenuEntries(exApp);
        }

        public static void WbOpen(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {
            SetCalcMenu(exApp, wb, sh);
            SetKontextMenu(exApp, wb, sh);
        }

        public static void WbSheetActivate(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {

            EnableDisableKontextMenu(exApp, sh);

            EnableDisableMenuGroups(exApp);

        }

        public static void WbSheetCalculate(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {

        }

        public static void WbSheetChange(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {
            ProjectSheet sheet = new ProjectSheet(sh, false);

            if ((exApp.ActiveCell.Address.Replace("$", "") == Settings.Default.ZelleKilometer) | (sheet.TempValue.Replace("$", "") == Settings.Default.ZelleKilometer)) {
                bool r1 = CheckKilometer(sh, false);
            }
        }

        public static void WbSheetDeactivate(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {

        }

        #endregion

        #region WorkSheet Events

        public static bool WSChange(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, myOffice.Excel.Range target, bool cancel) {
            try {
                if (target.Address.Replace("$", "") == Settings.Default.ZelleKilometer) {
                    cancel = CheckKilometer(sh, cancel);
                }
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "WSChange1:" + ex.Message, true);
            }
            return cancel;
        }

        public static void WSChange(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {
            try {
                ProjectSheet sheet = new ProjectSheet(sh, false);
                if ((exApp.ActiveCell.Address.Replace("$", "") == Settings.Default.ZelleKilometer) | (sheet.TempValue.Replace("$", "") == Settings.Default.ZelleKilometer)) {
                    bool r1 = CheckKilometer(sh, false);
                }
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "WSChange2:" + ex.Message, true);
            }
        }

        public static bool WSFollowHyperlink(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, myOffice.Excel.Range target, bool cancel) {
            bool ret = true;
            string s1, s2;

            myOffice.Excel.Hyperlink hl = target.Hyperlinks[1];
            if (hl != null) {
                s1 = hl.Address;
                s2 = hl.TextToDisplay;
            }

            if (!File.Exists(hl.Address)) {
                MessageBox.Show("File nicht vorhanden");
            }
            return ret;
        }

        #endregion

        #region Methods

        public static void AddMenuEntries() {
            try {
                if (GlobalVar.MenuEntries.Count == 0) {

                    //Neues Arbeitsblatt (int)faceIds.NewSheet
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 1, GroupHeader = true, MenuName = "GruppeSheet", Caption = "Neues Arbeitsblatt", FaceID = (int)faceIDs.NewSheet, Enabled = true, NewGroup = true, Visibly = true, EverySheet = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 1, MenuName = "EinfügenAP", Caption = "Abnahmeprotokoll", OnAction = "VBSub_2", FaceID = 0, Enabled = true, NewGroup = false, Visibly = false, Kalkulation = true, Abrechnung = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 1, MenuName = "EinfügenAbrechnung", Caption = "Abrechnung", OnAction = "VBSub_16", FaceID = 0, Enabled = true, NewGroup = false, Visibly = false, Kalkulation = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 1, MenuName = "EinfügenAngebot", Caption = "Angebot", OnAction = "VBSub_1", FaceID = 0, Enabled = true, NewGroup = false, Visibly = true, Kalkulation = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 1, MenuName = "EinfügenAuftragsbestätigung", Caption = "Auftragsbestaetigung", OnAction = "VBSub_14", FaceID = 0, Enabled = true, NewGroup = false, Visibly = false, Kalkulation = true, Abrechnung = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 1, MenuName = "EinfügenProject", Caption = "Geschäftsfall neu", OnAction = "VBSub_5", FaceID = 0, Enabled = true, NewGroup = false, Visibly = true, EverySheet = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 1, MenuName = "EinfügenKalkulation", Caption = "Kalkulation", OnAction = "VBSub_19", FaceID = 0, Enabled = true, NewGroup = false, Visibly = true, Kalkulation = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 1, MenuName = "EinfügenProjektBericht", Caption = "Leistungsbericht", OnAction = "VBSub_4", FaceID = 0, Enabled = true, NewGroup = false, Visibly = false, Kalkulation = true, Abrechnung = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 1, MenuName = "EinfügenLieferschein", Caption = "Lieferschein", OnAction = "VBSub_3", FaceID = 0, Enabled = true, NewGroup = false, Visibly = false, Kalkulation = true, Abrechnung = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 1, MenuName = "EinfügenRechnung", Caption = "Rechnung", OnAction = "VBSub_15", FaceID = 0, Enabled = true, NewGroup = false, Visibly = false, Kalkulation = true, Abrechnung = true });

                    //Funktion 
                    //GlobalVar.AddMenu(new MenuEintrag { GroupID = 6, GroupHeader = true, MenuName = "GruppeFunction", Caption = "Funktion", FaceID = (int)faceIDs.Funktion, Enabled = true, NewGroup = true, Visibly = true, EverySheet = true });
                    //GlobalVar.AddMenu(new MenuEintrag { GroupID = 6, MenuName = "AuswahlKA", Caption = "Auswahl KA", OnAction = "VBSub_12", FaceID = 0, Enabled = true, NewGroup = false, Visibly = false, Kalkulation = true, Abrechnung = false });

                    //Export (int)faceIDs.Export
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 2, GroupHeader = true, MenuName = "GruppeExport", Caption = "Export + Funktion", FaceID = (int)faceIDs.Export, Enabled = true, NewGroup = true, Visibly = true, EverySheet = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 2, MenuName = "AbrLieferant", Caption = "Abrechnung Lieferant", OnAction = "VBSub_6", FaceID = 0, Enabled = true, NewGroup = false, Visibly = false, Abrechnung = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 2, MenuName = "EinfügenGeschäftsfall", Caption = "Evidenz Geschäftsfälle", OnAction = "VBSub_12", FaceID = 0, Enabled = false, NewGroup = false, Visibly = false, Kalkulation = true, Abrechnung = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 2, MenuName = "EinfügenProjekt", Caption = "Evidenz Projekte", OnAction = "VBSub_17", FaceID = 0, Enabled = false, NewGroup = false, Visibly = false, Kalkulation = true, Abrechnung = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 2, MenuName = "ExportLeistungenProjekt", Caption = "Jobverwaltung", OnAction = "VBSub_7", FaceID = 0, Enabled = true, NewGroup = false, Visibly = false, Kalkulation = true, Abrechnung = true });
                    //GlobalVar.AddMenu(new MenuEintrag { GroupID = 2, MenuName = "EinfügenLeistungen", Caption = "Leistungen", OnAction = "VBSub_8", FaceID = 0, Enabled = true, NewGroup = false, Visibly = false, Abrechnung = true, Kalkulation = true });

                    //Dokument (int)faceIDs.Dokument
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 3, GroupHeader = true, MenuName = "GruppeExportDokument", Caption = "Export + Funktion + Dokument", FaceID = (int)faceIDs.Dokument, Enabled = true, NewGroup = true, Visibly = true, EverySheet = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 3, MenuName = "DokAngebot", Caption = "Angebot Dokument", OnAction = "VBSub_9", FaceID = 0, Enabled = true, NewGroup = false, Visibly = true, Angebot = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 3, MenuName = "DokAuftragsbestätigung", Caption = "Auftragsbestaetigung Dokument", OnAction = "VBSub_10", FaceID = 0, Enabled = true, NewGroup = false, Visibly = true, Auftragsbestaetigung = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 3, MenuName = "DokLieferant", Caption = "Lieferantenauftrag Dokument", OnAction = "VBSub_18", FaceID = 0, Enabled = false, NewGroup = false, Visibly = true, Abrechnung = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 3, MenuName = "DokMahnung", Caption = "Mahnung Dokument", OnAction = "VBSub_13", FaceID = 0, Enabled = true, NewGroup = false, Visibly = true, Abrechnung = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 3, MenuName = "DokRechnung", Caption = "Rechnung Dokument", OnAction = "VBSub_11", FaceID = 0, Enabled = true, NewGroup = false, Visibly = true, Rechnung = true });


                    //Import Ka (int)faceIDs.Import
                    //GlobalVar.AddMenu(new MenuEintrag { GroupID = 4, GroupHeader = true, MenuName = "GruppeImportKa", Caption = "Import Ka", FaceID = (int)faceIDs.Import, Enabled = true, NewGroup = true, Visibly = true, EverySheet = true });
                    //GlobalVar.AddMenu(new MenuEintrag { GroupID = 4, MenuName = "ImportKa", Caption = "Import aus Ka", OnAction = "VBSub_12", FaceID = 0, Enabled = false, NewGroup = false, Visibly = true, Kalkulation = true });

                    ////Bearbeitung Ka (int)faceIDs.Import
                    //GlobalVar.AddMenu(new MenuEintrag { GroupID = 5, GroupHeader = true, MenuName = "GruppeBearbeitungKa", Caption = "Bearbeitung Ka", FaceID = (int)faceIDs.Bearbeitung, Enabled = true, NewGroup = true, Visibly = true, EverySheet = true });
                    //GlobalVar.AddMenu(new MenuEintrag { GroupID = 5, MenuName = "BearbeiteKa", Caption = "Pr / Le - EInzeln - Gruppen", OnAction = "VBSub_13", FaceID = 0, Enabled = false, NewGroup = false, Visibly = true, Kalkulation = true });

                }
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "AddMenuEntries:" + ex.Message, true);
            }
        }

        public static void CopyFaktoren(Worksheet ka, Worksheet ab) {
            try {
                ((Microsoft.Office.Interop.Excel._Worksheet)ka).Activate();
                string rows = Settings.Default.ZeileFaktorenStart.ToString() + ":" + Settings.Default.ZeileFaktorenEnde.ToString();
                myOffice.Excel.Range r1 = ka.Rows[rows];
                r1.Select();
                r1.Copy();

                ((Microsoft.Office.Interop.Excel._Worksheet)ab).Activate();
                myOffice.Excel.Range r2 = ab.Cells[1, 1];
                r2.Select();
                r2.PasteSpecial(XlPasteType.xlPasteFormulas, XlPasteSpecialOperation.xlPasteSpecialOperationNone);

                myOffice.Excel.Range r3 = ab.Cells[1, 1];
                r3.Value = "An";

                r3 = ab.Cells[1, 3];
                r3.Value = "";
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CopyFaktoren:" + ex.Message, true);
            }
        }

        public static void CopyProduktZeile(myOffice.Excel.Range row, Worksheet target, int copyRow, bool sum) {
            CopyProduktZeile(row, target, copyRow, sum, false, false, "");
        }

        public static void CopyProduktZeile(myOffice.Excel.Range row, Worksheet target, int copyRow, bool sum, bool stellvertreter, bool isJe, string sourceFullname) {
            try {
                row.Select();
                row.Copy();
                ((Microsoft.Office.Interop.Excel._Worksheet)target).Activate();
                myOffice.Excel.Range targetRows = target.Rows[copyRow];
                targetRows.Select();
                targetRows.PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks: false, Transpose: false);
                targetRows.PasteSpecial(XlPasteType.xlPasteComments, XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks: false, Transpose: false);
                if (!sum) {
                    GlobalVar.AddProduktZeile("R" + copyRow.ToString() + "C" + Settings.Default.SpalteSummeVp.ToString());
                }
                if (stellvertreter) {
                    //targetRows.Font.Color = Settings.Default.Rot;
                    myOffice.Excel.Range r1 = row.Cells[1, "I"];
                    myOffice.Excel.Range t1 = targetRows.Cells[1, "N"];
                    t1.Value = r1.Value;

                }
                if (isJe) {
                    SetSpecialRowSettings(sourceFullname, targetRows);
                }

            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CopyProduktZeile:" + ex.Message, true);
            }
        }

        public static bool CopyRabattZeile(myOffice.Excel.Range row, Worksheet target, int copyRow) {
            try {
                ProjectRow selRow = new ProjectRow(row);
                if (selRow.SummeVp != 0 | selRow.Ust | selRow.RabattProzent != 0.0) {
                    CopyProduktZeile(row, target, copyRow, false);
                    myOffice.Excel.Range r1 = target.Rows[copyRow];
                    myOffice.Excel.Range rab1 = r1.Cells[1, Settings.Default.SpalteRabatt];
                    rab1.Value = selRow.RabattProzent;
                    myOffice.Excel.Range rab2 = r1.Cells[1, Settings.Default.SpalteSummeVp];
                    if (selRow.Ust) {
                        rab2.FormulaR1C1 = "=R" + ((int)(copyRow - 1)).ToString() + "C" + Settings.Default.SpalteSummeVp.ToString() + "*R" + copyRow.ToString() + "C" + Settings.Default.SpalteRabatt.ToString() + "/100";
                        target.Names.Add("Umsatzsteuer", rab2);
                    } else {
                        //if (selRow.RabattProzent != 0.0) {
                        //    if (GlobalVar.Rabatte.Count > 0) {
                        //        rab2.FormulaR1C1 = "=Sum(R" + ((int)(copyRow - GlobalVar.Rabatte.Count - 1)).ToString() + "C" + Settings.Default.SpalteSummeVp.ToString() +":" + "R" + (copyRow - 1) .ToString() + "C" + Settings.Default.SpalteSummeVp.ToString() + ")*R" + copyRow.ToString() + "C" + Settings.Default.SpalteRabatt.ToString() + "/100*-1";
                        //    } else {
                        //        rab2.FormulaR1C1 = "=R" + ((int)(copyRow - 1)).ToString() + "C" + Settings.Default.SpalteSummeVp.ToString() + "*R" + copyRow.ToString() + "C" + Settings.Default.SpalteRabatt.ToString() + "/100*-1";
                        //    }
                        //} else {
                        //    rab2.Value = selRow.SummeVp;
                        //}
                        rab2.Value = selRow.SummeVp;
                    }
                    return true;
                }
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CopyRabattZeile:" + ex.Message, true);
            }
            return false;
        }

        private static void CreateApZeile(myOffice.Excel.Range row, Worksheet target, int copyRow) {
            try {
                ProjectRow selRow = new ProjectRow(row);

                if (copyRow >= Settings.Default.ZeileApMaxSecondPage) {
                    myOffice.Excel.Range newRow = target.Rows[copyRow];
                    newRow.Select();
                    newRow.Copy();
                    newRow.Select();
                    newRow.Insert(Shift: XlInsertShiftDirection.xlShiftDown);
                }

                myOffice.Excel.Range prLeText = target.Range[Settings.Default.SpalteApProdukteLeistung + copyRow.ToString()];
                prLeText.Value = selRow.RowText;

                myOffice.Excel.Range einheit = target.Range[Settings.Default.SpalteApEinheit + copyRow.ToString()];
                einheit.Value = selRow.Einheit;

                myOffice.Excel.Range menge = target.Range[Settings.Default.SpalteApMenge + copyRow.ToString()];
                menge.Value = selRow.Menge;

                myOffice.Excel.Range targetRow = target.Rows[copyRow];
                targetRow.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = 1;
                targetRow.Borders[XlBordersIndex.xlEdgeBottom].Weight = 1;
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CreateApZeile:" + ex.Message, true);
            }
        }

        private static void CreateApRepZeile(myOffice.Excel.Range row, Worksheet target, int copyRow) {
            try {
                ProjectRow selRow = new ProjectRow(row);
                myOffice.Excel.Range prLeText = target.Range[Settings.Default.SpalteApRepProdukteLeistung + copyRow.ToString()];
                prLeText.Value = selRow.RowText;

                if (selRow.Einheit.ToUpper() != "STD") {
                    myOffice.Excel.Range einheit = target.Range[Settings.Default.SpalteApRepEinheit + copyRow.ToString()];
                    einheit.Value = selRow.Einheit;

                    myOffice.Excel.Range menge = target.Range[Settings.Default.SpalteApRepMenge + copyRow.ToString()];
                    menge.Value = selRow.Menge;
                }
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CreateApRepZeile:" + ex.Message, true);
            }
        }

        public static void CreateDifference(Worksheet ka, Worksheet target, int copyRow) {
            try {
                ProjectSheet sheet = new ProjectSheet(ka, false);
                myOffice.Excel.Range r1 = target.Cells[copyRow, Settings.Default.SpalteText];
                r1.Value = "Differenz";
                //r1.Font.Name = Extensions.tpFontBold;
                Extensions.SetFontBoldOrNormal(r1, Extensions.tpFont.Bold);
                myOffice.Excel.Range r2 = target.Cells[copyRow, Settings.Default.SpalteSummeVp];
                if (sheet.HasNames) {
                    r2.FormulaR1C1 = "=R" + Convert.ToString(copyRow - 1) + "C" + Settings.Default.SpalteSummeVp.ToString() + "-" + ka.Name + "!SummeBrutto";
                } else {
                    r2.Value = Resources.CreateDifferenceErrorWithoutSummeBrutto;
                }
                r2.Font.ThemeColor = 7;
                //r2.Font.Name = Extensions.tpFontBold;
                Extensions.SetFontBoldOrNormal(r2, Extensions.tpFont.Bold);

                myOffice.Excel.Range r3 = ka.Range[ka.Cells[Settings.Default.ZeileStart, Settings.Default.SpalteText], ka.Cells[copyRow - 1, Settings.Default.SpalteSummeVp]];
                target.PageSetup.PrintArea = r3.Address;
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CreateDifference:" + ex.Message, true);
            }
        }

        private static void CreateLsZeile(myOffice.Excel.Range row, Worksheet target, int copyRow) {
            try {
                ProjectRow selRow = new ProjectRow(row);

                if (copyRow >= Settings.Default.ZeileLsMaxFirstPage) {
                    myOffice.Excel.Range newRow = target.Rows[copyRow];
                    newRow.Select();
                    newRow.Copy();
                    newRow.Select();
                    newRow.Insert(Shift: XlInsertShiftDirection.xlShiftDown);
                }

                myOffice.Excel.Range prLeText = target.Range[Settings.Default.SpalteLsProdukteLeistung + copyRow.ToString()];
                prLeText.Value = selRow.RowText;

                myOffice.Excel.Range einheit = target.Range[Settings.Default.SpalteLsEinheit + copyRow.ToString()];
                einheit.Value = selRow.Einheit;

                myOffice.Excel.Range menge = target.Range[Settings.Default.SpalteLsMenge + copyRow.ToString()];
                menge.Value = selRow.Menge;

                myOffice.Excel.Range targetRow = target.Rows[copyRow];
                targetRow.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = 1;
                targetRow.Borders[XlBordersIndex.xlEdgeBottom].Weight = 1;
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CreateLsZeile:" + ex.Message, true);
            }
        }

        public static void CreateNewCalc(Worksheet sh) {
            TpMenu myMenu = GetTpMenu(sh);
            CalcMenu aWindow = new CalcMenu(myMenu);
            aWindow.ShowDialog();
            if (!aWindow.Cancel) {
                try {
                    DeleteEmptyRows(sh, aWindow.SelectedColumn);
                    myOffice.Excel.Range r1 = sh.Cells[3, 1];
                    r1.Value = "Doc";
                } catch (Exception ex) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CreateNewCalc:" + ex.Message, true);
                }
            }
        }

        public static void CreateRechnungsDifference(Workbook wb, Worksheet ka, Worksheet target, int copyRow) {
            try {
                ProjectSheet sheet = new ProjectSheet(ka, false);
                myOffice.Excel.Range r1 = target.Cells[copyRow, Settings.Default.SpalteText];
                r1.Value = "Differenz";
                //r1.Font.Name = Extensions.tpFontBold;
                Extensions.SetFontBoldOrNormal(r1, Extensions.tpFont.Bold);
                string formel = "";
                foreach (Worksheet s1 in wb.Sheets) {
                    ProjectSheet sheet1 = new ProjectSheet(s1, false);
                    if (sheet1.SheetName.Contains("f") & sheet1.ProjektNummer == sheet.ProjektNummer & sheet1.SheetName != target.Name) {
                        if (sheet1.HasNames) {
                            formel += "+" + s1.Name + "!SummeBrutto";
                        }
                    }
                }

                myOffice.Excel.Range r2 = target.Cells[copyRow, Settings.Default.SpalteSummeVp];
                if (sheet.HasNames) {
                    r2.FormulaR1C1 = "=R" + Convert.ToString(copyRow - 1) + "C" + Settings.Default.SpalteSummeVp.ToString() + "-" + ka.Name + "!SummeBrutto" + formel;
                } else {
                    r2.Value = Resources.CreateDifferenceErrorWithoutSummeBrutto;
                }
                r2.Font.ThemeColor = 7;
                //r2.Font.Name = Extensions.tpFontBold;
                Extensions.SetFontBoldOrNormal(r2, Extensions.tpFont.Bold);

                myOffice.Excel.Range r3 = ka.Range[ka.Cells[Settings.Default.ZeileStart, Settings.Default.SpalteText], ka.Cells[copyRow - 1, Settings.Default.SpalteSummeVp]];
                target.PageSetup.PrintArea = r3.Address;
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "CreateRechnungsDifference(" + ka.Name + ") :" + ex.Message, true);
            }
        }

        public static bool CreateSummenZeile(myOffice.Excel.Range row, ProjectRow selRow, Worksheet ka, Worksheet target, int copyRow, int lastSummenRow) {
            return CreateSummenZeile(row, selRow, ka, target, copyRow, lastSummenRow, true);
        }

        public static bool CreateSummenZeile(myOffice.Excel.Range row, ProjectRow selRow, Worksheet ka, Worksheet target, int copyRow, int lastSummenRow, bool visibleRow) {
            try {
                if (((selRow.RowType.ToUpper() == "SU") | (selRow.RowType == "S4")) & (selRow.IsSummenZeile)) {

                    int sHierachie = selRow.SummenHierachie;
                    myOffice.Excel.Range r1 = target.Rows[copyRow];
                    myOffice.Excel.Range r2 = r1.Cells[1, Settings.Default.SpalteSummeVp];

                    switch (sHierachie) {
                        case 1:
                            if (selRow.BildeSumme) {
                                CopyProduktZeile(row, target, copyRow, true);
                                r2.FormulaR1C1 = "=Sum(R" + (lastSummenRow == 0 ? GlobalVar.SummenStartRow.ToString() : (string)(lastSummenRow + 1).ToString()) + "C" + Settings.Default.SpalteSummeVp + ":R" + (string)(copyRow - 1).ToString() + "C" + Settings.Default.SpalteSummeVp.ToString() + ")";
                                GlobalVar.RemoveAllProduktZeilen();
                                SetGlobalSummen(copyRow, copyRow, 1);
                                SetSummenZeile(target, copyRow);
                                return true;
                            }
                            return false;
                        case 2:
                            if (selRow.BildeSumme) {
                                CopyProduktZeile(row, target, copyRow, true);
                                r2.FormulaR1C1 = GetGesamtSummenFormel(2);
                                if (GlobalVar.ProduktZeilenAnzahl > 0) {
                                    string s1 = GetGesamtSummenFormel(1).Replace("=", "+");
                                    r2.FormulaR1C1 += s1;
                                }
                                GlobalVar.RemoveAllProduktZeilen();
                                SetGlobalSummen(copyRow, copyRow, 2);
                                SetSummenZeile(target, copyRow);
                                return true;
                            }
                            return false;
                        case 3:
                            if (selRow.BildeSumme) {
                                CopyProduktZeile(row, target, copyRow, true);
                                r2.FormulaR1C1 = GetGesamtSummenFormel(3);

                                if (GlobalVar.ProduktZeilenAnzahl > 0) {
                                    string s1 = GetGesamtSummenFormel(1).Replace("=", "+");
                                    r2.FormulaR1C1 += s1;
                                }
                                GlobalVar.RemoveAllProduktZeilen();
                                SetGlobalSummen(copyRow, 0, 3);
                                SetSummenZeile(target, copyRow);
                                return true;
                            }
                            return false;
                        case 4:
                            if (selRow.BildeSumme) {
                                CopyProduktZeile(row, target, copyRow, true);
                                switch (selRow.S4Type.ToUpper()) {
                                    case "GESAMT":
                                        GlobalVar.SummeGesamtFormel = GetGesamtSummenFormel(4);
                                        r2.FormulaR1C1 = GlobalVar.SummeGesamtFormel;
                                        GlobalVar.S4SummenStart = copyRow;
                                        break;
                                    case "SUMME NETTO":

                                        r2.FormulaR1C1 = "=Sum(R" + GlobalVar.S4SummenStart.ToString() + "C" + Settings.Default.SpalteSummeVp.ToString() + ":R" + ((int)copyRow - 1).ToString() + "C" + Settings.Default.SpalteSummeVp.ToString() + ")";
                                        target.Names.Add("SummeNetto", r2);
                                        if (!GlobalVar.CopyRabatt) {
                                            copyRow--;
                                            myOffice.Excel.Range rowBrutto = target.Rows[copyRow];
                                            rowBrutto.Delete();
                                            r2.FormulaR1C1 = GlobalVar.SummeGesamtFormel;
                                            SetSummenZeile(target, copyRow);
                                            return false;
                                        }

                                        break;
                                    case "SUMME BRUTTO":
                                        r2.FormulaR1C1 = "=Sum(R" + ((int)copyRow - 2).ToString() + "C" + Settings.Default.SpalteSummeVp.ToString() + ":R" + ((int)copyRow - 1).ToString() + "C" + Settings.Default.SpalteSummeVp.ToString() + ")";
                                        GlobalVar.RemoveSummenAddress(4, true);
                                        target.Names.Add("SummeBrutto", r2);
                                        break;
                                }
                                SetSummenZeile(target, copyRow, visibleRow);
                                return true;
                            }
                            return false;
                        default:
                            break;

                    }
                }
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, ka.Name + ", " + "CreateSummenZeile(" + selRow.RowText + "): " + ex.Message, true);
            }
            return false;
        }

        private static void DeleteCustomsProperty(Worksheet sh, string propertyName) {
            foreach (myOffice.Excel.CustomProperty prop in sh.CustomProperties) {
                if (prop.Name == propertyName) {
                    prop.Delete();
                }
            }
        }

        private static void DeleteMenuEntries(Microsoft.Office.Interop.Excel.Application exApp) {
            DeleteMenuEntries(exApp, null);
        }

        private static void DeleteMenuEntries(Microsoft.Office.Interop.Excel.Application exApp, string menuName) {
            if (menuName == null) {
                foreach (MenuEintrag me in GlobalVar.MenuEntries) {
                    if (KontextMenuControlExists(exApp, me.Caption)) {
                        exApp.CommandBars[GlobalVar.tpContextMenuName].Controls[me.Caption].Delete();
                    }
                }
            } else {
                foreach (MenuEintrag me in GlobalVar.MenuEntries) {
                    if (me.Caption == menuName) {
                        if (KontextMenuControlExists(exApp, me.Caption)) {
                            exApp.CommandBars[GlobalVar.tpContextMenuName].Controls[me.Caption].Delete();
                        }
                    }
                }
            }
        }

        private static void EnableDisableKontextMenu(Microsoft.Office.Interop.Excel.Application exApp, Worksheet sh) {
            foreach (MenuEintrag me in GlobalVar.MenuEntries) {
                if (KontextMenuControlExists(exApp, me.Caption)) {
                    exApp.CommandBars[GlobalVar.tpContextMenuName].Controls[me.Caption].Enabled = false;
                }
            }
            GlobalVar.RemoveAllMenuGroups();

            ProjectSheet sheet = new ProjectSheet(sh, false);
            Extensions.SheetType selType = (Extensions.SheetType)sheet.SheetType;
            switch (selType) {
                case Extensions.SheetType.Kalkulation:
                    EnableDisableKontextMenu(exApp, Extensions.SheetType.Kalkulation.GetStringValue());
                    if (sheet.CalcType == "K") {
                        EnableDisableMenuEntry(exApp, Extensions.SheetType.Auftragsbestaetigung.GetStringValue(), GlobalVar.tpContextMenuName, false);
                    }
                    break;
                case Extensions.SheetType.Angebot:
                    EnableDisableKontextMenu(exApp, Extensions.SheetType.Angebot.GetStringValue());
                    break;
                case Extensions.SheetType.Auftragsbestaetigung:
                    EnableDisableKontextMenu(exApp, Extensions.SheetType.Auftragsbestaetigung.GetStringValue());
                    break;
                case Extensions.SheetType.Rechnung:
                    EnableDisableKontextMenu(exApp, Extensions.SheetType.Rechnung.GetStringValue());
                    break;
                case Extensions.SheetType.Projektbericht:
                    EnableDisableKontextMenu(exApp, Extensions.SheetType.Projektbericht.GetStringValue());
                    break;
                case Extensions.SheetType.Abnahmeprotokoll:
                    EnableDisableKontextMenu(exApp, Extensions.SheetType.Abnahmeprotokoll.GetStringValue());
                    break;
                case Extensions.SheetType.Abrechnung:
                    EnableDisableKontextMenu(exApp, Extensions.SheetType.Abrechnung.GetStringValue());
                    break;
                case Extensions.SheetType.Lieferschein:
                    EnableDisableKontextMenu(exApp, Extensions.SheetType.Lieferschein.GetStringValue());
                    break;
                default:
                    EnableDisableKontextMenu(exApp, "EverySheet");
                    break;
            }
        }

        private static void EnableDisableKontextMenu(Microsoft.Office.Interop.Excel.Application exApp, string menuName) {
            foreach (MenuEintrag me in GlobalVar.MenuEntries) {
                if (me.Enabled) {
                    Type typeMenu = me.GetType();
                    foreach (PropertyInfo pInfo in typeMenu.GetProperties()) {
                        if ((pInfo.Name == menuName) | (pInfo.Name == Extensions.SheetType.EverySheet.GetStringValue())) {
                            bool setValue = false;
                            setValue = (bool)pInfo.GetValue(me, null);
                            if (setValue) {
                                if (KontextMenuControlExists(exApp, me.Caption)) {
                                    exApp.CommandBars[GlobalVar.tpContextMenuName].Controls[me.Caption].Enabled = true;
                                    if (!me.GroupHeader) {
                                        GlobalVar.AddMenuGroups(me.GroupID);
                                    }
                                }
                            }
                        }
                    }
                } else {
                    if (KontextMenuControlExists(exApp, me.Caption)) {
                        exApp.CommandBars[GlobalVar.tpContextMenuName].Controls[me.Caption].Enabled = false;
                    }
                }
            }
        }

        private static void EnableDisableMenuGroups(Microsoft.Office.Interop.Excel.Application exApp) {
            foreach (MenuEintrag me in GlobalVar.MenuEntries) {
                if (me.GroupHeader) {
                    bool setValue = GlobalVar.EnableMenuGroup(me.GroupID);
                    if (KontextMenuControlExists(exApp, me.Caption)) {
                        exApp.CommandBars[GlobalVar.tpContextMenuName].Controls[me.Caption].Enabled = setValue;
                    }
                }
            }
        }

        private static void EnableDisableMenuEntry(Microsoft.Office.Interop.Excel.Application exApp, string caption, string commandBarName, bool enable) {
            exApp.CommandBars[commandBarName].Controls[caption].Enabled = enable;
        }

        public static void ExportBusinessCase2Db(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {
            ProjectSheet sheet = new ProjectSheet(sh, true, true, false);
            BusinessCaseModel bcm = new BusinessCaseModel();
            bcm.ShortName = sheet.OrgName + sheet.Projekt;
            bcm.CostAdministration = sheet.AeKostenSummeAdministration;
            bcm.CostHandelsware = sheet.AeKostenSummeHandelsware;
            bcm.CostMontage = sheet.AeKostenSummeMontage;
            bcm.CostProduktion = sheet.AeKostenSummeProduktion;

            bcm.ErloesEm = sheet.AeErlösMinderung;
            bcm.ErloesFa = sheet.AeForderungsausfall;
            bcm.ErloesRaLe = sheet.RabattProdukteProzent;
            bcm.ErloesRaPr = sheet.RabattProzent;
            bcm.ErloesRaPro = sheet.RabattZusatzProzent;
            bcm.ErloesSf = sheet.AeSchadenfall;
            bcm.ErloesSuHandelsware = sheet.AeSummeHandelsware;
            bcm.ErloesSuMontage = sheet.AeSummeMontage;
            bcm.ErloesSuProduktion = sheet.AeSummeProduktion;
            bcm.ExtraCostBIFix = sheet.AeZusatzKostenBaustelleninfrastrukturFix;
            bcm.ExtraCostBIPercent = sheet.AeZusatzKostenBaustelleninfrastrukturProzent;
            bcm.ExtraCostErfGar = sheet.AeZusatzkostenErfüllungsgarantie;
            bcm.ExtraCostHaftRuecklage = sheet.AeZusatzkostenHaftrücklass;
            bcm.ExtraCostPrEFix = sheet.AeZusatzKostenProvisionsErlösFix;
            bcm.ExtraCostPrEPercent = sheet.AeZusatzKostenProvisionsErlösProzent;
            bcm.ExtraCostProvisionDB = sheet.AeZusatzkostenProvsionDB;
            bcm.ExtraCostSkonto = sheet.AeZusatzkostenSkonto;
            bcm.TimeAdministration = sheet.AeZeitaufwandAdminsitration;
            bcm.TimeMontage = sheet.AeZeitaufwandMontage;
            bcm.TimeProduktion = sheet.AeZeitaufwandProdutktion;
            bcm.TimeProducts = sheet.AeZeitaufwandProdukte;

            BusinessCaseDataManager.UpdateBusinessCase(bcm);

        }

        public static string GetGesamtSummenFormel(byte type) {
            string ret = "=";
            switch (type) {
                case 1:
                    foreach (string s1 in GlobalVar.ProduktZeilen) {
                        ret += (ret.Length > 1 ? "+" : "") + s1;
                    }
                    break;
                case 2:
                    foreach (string s1 in GlobalVar.S2SummenZellen) {
                        ret += (ret.Length > 1 ? "+" : "") + s1;
                    }
                    break;
                case 3:
                    if (GlobalVar.S3SummenZellen.Count == 0) {
                        goto case 2;
                    }
                    foreach (string s1 in GlobalVar.S3SummenZellen) {
                        ret += (ret.Length > 1 ? "+" : "") + s1;
                    }
                    break;
                case 4:
                    foreach (string s1 in GlobalVar.SummenZellen) {
                        ret += (ret.Length > 1 ? "+" : "") + s1;
                    }
                    foreach (string s1 in GlobalVar.S3SummenZellen) {
                        ret += (ret.Length > 1 ? "+" : "") + s1;
                    }
                    if (GlobalVar.S3SummenZellen.Count == 0) {
                        foreach (string s1 in GlobalVar.S2SummenZellen) {
                            ret += (ret.Length > 1 ? "+" : "") + s1;
                        }
                    }
                    if (GlobalVar.S2SummenZellen.Count == 0) {
                        foreach (string s1 in GlobalVar.S1SummenZellen) {
                            ret += (ret.Length > 1 ? "+" : "") + s1;
                        }
                    }
                    if (GlobalVar.ProduktZeilenAnzahl > 0) {
                        foreach (string s1 in GlobalVar.ProduktZeilen) {
                            ret += (ret.Length > 1 ? "+" : "") + s1;
                        }
                    }

                    break;
            }
            return ret;
        }

        public static void KontextMenuAddControl(Microsoft.Office.Interop.Excel.Application exApp) {

            foreach (MenuEintrag me in GlobalVar.MenuEntries) {
                try {
                    if (!KontextMenuControlExists(exApp, me.Caption)) {
                        CommandBarButton cbb = (CommandBarButton)exApp.CommandBars[GlobalVar.tpContextMenuName].Controls.Add();
                        cbb.Caption = me.Caption;
                        cbb.OnAction = me.OnAction;
                        cbb.FaceId = me.FaceID;
                        cbb.BeginGroup = me.NewGroup;
                        cbb.Enabled = me.Enabled;
                        cbb.Height = 0;
                    }
                } catch (Exception ex) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "KontextMenuAddControl(" + me.Caption + "): " + ex.Message, true);
                }
            }
        }

        private static bool KontextMenuControlExists(Microsoft.Office.Interop.Excel.Application exApp, string caption) {
            foreach (CommandBarButton cbb in exApp.CommandBars[GlobalVar.tpContextMenuName].Controls) {
                if (cbb.Caption == caption) {
                    return true;
                }
            }
            return false;
        }

        public static void KontextMenuRemoveControl(Microsoft.Office.Interop.Excel.Application exApp, bool delete) {
            try {
                if (delete) {
                    foreach (GlobalVar.MenuEntry menu in GlobalVar.Elements) {
                        if (CBControlExists(exApp.CommandBars[GlobalVar.tpContextMenuName], menu.Caption)) {
                            exApp.CommandBars[GlobalVar.tpContextMenuName].Controls[menu.Caption].Delete();
                        }
                    }
                    return;
                }
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "KontextMenuRemoveControl:" + ex.Message, true);
            }
        }

        public static void SetCalcMenu(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {
            string t1 = "";
            myOffice.Excel.Range r1 = sh.Cells[1, 1];
            if (r1.Value != null) {
                t1 = Convert.ToString(r1.Value);
            }

            if (t1.Contains("Ka")) {
                r1 = sh.Cells[3, 1];
                if (r1.Value == null & wb.Sheets.Count < 2 & wb.FileFormat == XlFileFormat.xlOpenXMLWorkbookMacroEnabled) {
                    CreateNewCalc(sh);
                }
            }
        }

        private static void SetDataAbnahmeprotokoll(Organisation org, Worksheet ap, ProjectSheet sheet, bool isRep) {
            try {
                myOffice.Excel.Range apName = (isRep ? ap.Range[Settings.Default.ZelleApRepName] : ap.Range[Settings.Default.ZelleApName]);
                apName.Value = org.Prefix.AddSpace() + org.OrgName + " " + org.ZusätzeFW.AddSpace() + org.RechtsForm;

                myOffice.Excel.Range apDateiSuchbegriff = (isRep ? ap.Range[Settings.Default.ZelleApRepDateiSuchbegriff] : ap.Range[Settings.Default.ZelleApDateiSuchbegriff]);
                apDateiSuchbegriff.Value = org.Suchbegriff;

                myOffice.Excel.Range apDateiAbteilung = (isRep ? ap.Range[Settings.Default.ZelleApRepDateiAbteilung] : ap.Range[Settings.Default.ZelleApDateiAbteilung]);
                apDateiAbteilung.Value = org.Abteilung;

                myOffice.Excel.Range apAdresse = (isRep ? ap.Range[Settings.Default.ZelleApRepAdresse] : ap.Range[Settings.Default.ZelleApAdresse]);
                apAdresse.Value = org.Plz.AddSpace() + org.Ort + ", " + org.Straße;
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "SetDataAbnahmeprotokoll:" + ex.Message, true);
            }
        }

        public static void SetDataProjektBericht(Organisation org, Worksheet pb, ProjectSheet sheet) {
            try {
                if (org != null) {
                    //Kunde
                    myOffice.Excel.Range r1 = pb.Range["A4"];
                    r1.Value = org.OrgName;

                    //Suchbegriff
                    r1 = pb.Range["AM4"];
                    r1.Value = org.Suchbegriff;

                    //Projektnummer
                    r1 = pb.Range["AR4"];
                    r1.Value = "P" + sheet.ProjektNummer.ToString();

                    //Adresse
                    r1 = pb.Range["U4"];
                    r1.Value = org.Plz + " " + org.Ort + ", " + org.Straße;

                    //Telefon Festnetz
                    r1 = pb.Range["U6"];
                    r1.Value = org.TelNr1;

                    //Telefon Mobil
                    r1 = pb.Range["AC6"];
                    r1.Value = org.TelNr2;
                }
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "SetDataProjektBericht:" + ex.Message, true);
            }
        }

        public static void SetDataLohnfertigungBericht(Organisation org, Worksheet pb, ProjectSheet sheet) {
            try {
                if (org != null) {
                    //Kunde
                    myOffice.Excel.Range r1 = pb.Range["Q2"];
                    r1.Value = org.OrgName;
                }
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "SetDataLohnfertigungBericht:" + ex.Message, true);
            }
        }

        public static void SetGlobalSummen(int copyRow, int lastSummenRow, int hierachie) {
            try {
                string s1 = "R" + copyRow.ToString() + "C" + Settings.Default.SpalteSummeVp.ToString();
                GlobalVar.LastSummenRow = lastSummenRow;
                GlobalVar.MaxSummenHierachie = hierachie;
                GlobalVar.SummenStartRow = copyRow + 1;
                GlobalVar.PreviousSummenHierachie = hierachie;
                GlobalVar.AddSummenAddress(1, s1);

                if (hierachie == 1) {
                    GlobalVar.AddSummenAddress(2, s1);
                }

                if (hierachie == 2) {
                    GlobalVar.SummenStartRow2 = copyRow + 1;
                    GlobalVar.AddSummenAddress(3, s1);
                    GlobalVar.RemoveSummenAddress(1, true);
                    GlobalVar.RemoveSummenAddress(2, true);
                }

                if (hierachie == 3) {
                    GlobalVar.SummenStartRow2 = copyRow + 1;
                    GlobalVar.SummenStartRow3 = copyRow + 1;
                    GlobalVar.PreviousSummenHierachie = 0;
                    GlobalVar.AddSummenAddress(4, s1);
                    GlobalVar.RemoveSummenAddress(1, true);
                    GlobalVar.RemoveSummenAddress(2, true);
                    GlobalVar.RemoveSummenAddress(3, true);
                }
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "SetClobalSummen:" + ex.Message, true);
            }
        }

        public static void SetKontextMenu(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {
            SetKontextMenu(exApp, wb, sh, false);
        }

        public static void SetKontextMenu(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, bool delete) {
            try {
                if (wb.FileFormat != XlFileFormat.xlOpenXMLWorkbookMacroEnabled & wb.FileFormat != XlFileFormat.xlOpenXMLTemplateMacroEnabled) {
                    DeleteMenuEntries(exApp);
                    return;
                }

                AddMenuEntries();

                KontextMenuAddControl(exApp);

                EnableDisableKontextMenu(exApp, sh);

                EnableDisableMenuGroups(exApp);

            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "SetKontextMenu(" + wb.Name + "): " + ex.Message, true);
            }
        }

        private static void SetDataLieferschein(Organisation org, Worksheet ap) {
            try {
                myOffice.Excel.Range apName = ap.Range[Settings.Default.ZelleLsName];
                apName.Value = org.Prefix.AddSpace() + org.OrgName + " " + org.ZusätzeFW.AddSpace() + org.RechtsForm;

                myOffice.Excel.Range apDateiSuchbegriff = ap.Range[Settings.Default.ZelleLsDateiSuchbegriff];
                apDateiSuchbegriff.Value = org.Suchbegriff;

                myOffice.Excel.Range apDateiAbteilung = ap.Range[Settings.Default.ZelleLsDateiAbteilung];
                apDateiAbteilung.Value = org.Abteilung;

                myOffice.Excel.Range apAdresse = ap.Range[Settings.Default.ZelleLsAdresse];
                apAdresse.Value = org.Plz.AddSpace() + org.Ort + ", " + org.Straße;

                myOffice.Excel.Range datum = ap.Range[Settings.Default.ZelleLsDatum];
                datum.Value = Tools.GetTpDateToday();
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "SetDataLieferschein: " + ex.Message, true);
            }
        }

        private static void SetSpecialRowSettings(string sourceFullname, myOffice.Excel.Range targetRows) {
            string shortCode = "";
            string shortCode2 = "";
            if (sourceFullname.Contains(@"\Kund\")) {
                shortCode = "Kund";
            }
            if (sourceFullname.Contains(@"\Verk\")) {
                shortCode = "Verk";
            }
            if (shortCode == "") {
                myOffice.Excel.Range c1 = targetRows.Cells[1, "C"];
                myOffice.Excel.Range c2 = targetRows.Cells[1, "D"];
                if (c1.Value != null) {
                    shortCode = Convert.ToString(c1.Value);
                }
                if (c2.Value != null) {
                    shortCode2 = Convert.ToString(c2.Value);
                }
            }

            myOffice.Excel.Range r2 = targetRows.Cells[1, "E"];
            r2.Value = "";
            r2 = targetRows.Cells[1, "F"];
            r2.Value = "";
            r2 = targetRows.Cells[1, "G"];
            r2.Value = "";
            r2 = targetRows.Cells[1, "H"];
            r2.Value = shortCode;
            r2 = targetRows.Cells[1, "W"];
            r2.Value = "";
            r2 = targetRows.Cells[1, "X"];
            r2.Value = "";
            r2 = targetRows.Cells[1, "BB"];
            r2.Value = sourceFullname;
            if (shortCode2 != "") {
                r2 = targetRows.Cells[1, "BC"];
                r2.Value = shortCode2;
            }
        }

        public static void SetSummenZeile(Worksheet sh, int row) {
            SetSummenZeile(sh, row, true);
        }

        public static void SetSummenZeile(Worksheet sh, int row, bool visibleRow) {
            //((Range)sh.Rows[row]).Font.Name = Extensions.tpFontBold;
            myOffice.Excel.Range r1 = sh.Rows[row];
            Extensions.SetFontBoldOrNormal(r1, Extensions.tpFont.Bold, true);
            r1.EntireRow.Hidden = !visibleRow;
        }

        public static void DeleteEmptyRows(Worksheet sh, int col) {
            try {
                int startRow = 301;
                int nextRow;
                myOffice.Excel.Range rLast = sh.get_Range("LastRow");
                myOffice.Excel.Range r1 = sh.Cells[startRow, col];
                int lastRow = rLast.Row;
                object toFind = null;
                myOffice.Excel.Range rCol = sh.Columns[col];

                for (int i = startRow; startRow < lastRow; i++) {
                    r1 = sh.Cells[i, col];
                    if (r1.Value == null) {
                        nextRow = i;
                    } else {
                        nextRow = GetNextRowNumber(sh, rCol, r1, toFind);
                    }
                    if (nextRow < i) {
                        return;
                    }
                    if (toFind == null) {
                        myOffice.Excel.Range r2 = sh.Cells[nextRow, col];
                        toFind = "1";
                        int delRow = GetNextRowNumber(sh, rCol, r2, toFind);
                        if (delRow < i) {
                            return;
                        }
                        myOffice.Excel.Range delRange = sh.Rows[nextRow.ToString() + ":" + (delRow - 1).ToString()];
                        delRange.Delete();
                        i = nextRow;
                        lastRow = lastRow - (delRow - nextRow);
                        toFind = null;
                    }
                }
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "DeleteEmptyRows: " + ex.Message, true);
            }
        }

        public static void TestRoutine(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {
            myOffice.Excel.Range row1 = sh.Rows[322];
            ProjectRow row = new ProjectRow(row1);

            myOffice.Excel.Range mr1 = row1.Cells[1, Settings.Default.SpalteZeilenArt];

            string ts1 = mr1.Value;

            string s1 = row.RowType;
            string r2 = row.RowTerm;
            string r3 = row.RowText;
            bool b1 = row.IsVpRow;
            bool b2 = row.IsEpRow;

            double d1 = row.SummeEp;
            double d2 = row.SummeVp;

        }

        public static void UpdateAeDetails(ProjectSheet mySheet, AeRow myRow) {
            try {
                #region Projekt Adresse

                if (mySheet.AuftragsDatum != new DateTime(1900, 1, 1)) {
                    myRow.AuftragsDatum = mySheet.AuftragsDatum;
                }
                myRow.ProjektName = mySheet.Kundenname;
                myRow.ProjektBezeichnung = mySheet.Projekt;
                myRow.Plz = mySheet.Plz.ToString();
                myRow.Ort = mySheet.Ort;
                myRow.Straße = mySheet.Straße;

                #endregion

                #region Erlös

                myRow.SummeHandelsware = mySheet.AeSummeHandelsware;
                myRow.SummeMontage = mySheet.AeSummeMontage;
                myRow.SummeProduktion = mySheet.AeSummeProduktion;
                myRow.RabattGesamt = mySheet.AeRabattProzent + mySheet.AeRabattFix + mySheet.AeZusatzRabattProzent;
                myRow.RabattLeistungenGesamt = mySheet.AeRabattLeistungenProzent + mySheet.AeRabattLeistungenFix;
                myRow.RabattProdukteGesamt = mySheet.AeRabattProdukteProzent + mySheet.AeRabattProdukteFix;
                myRow.Erlösminderung = mySheet.AeErlösMinderung;
                myRow.Forderungsausfall = mySheet.AeForderungsausfall;
                myRow.Schadenfall = mySheet.AeSchadenfall;

                #endregion

                #region Kosten

                myRow.KostenAdministration = mySheet.AeKostenSummeAdministration;
                myRow.KostenHandelsware = mySheet.AeKostenSummeHandelsware;
                myRow.KostenMontage = mySheet.AeKostenSummeMontage;
                myRow.KostenProduktion = mySheet.AeKostenSummeProduktion;

                #endregion

                #region Zusatzkosten

                myRow.ZkBaustellenInfrastrukturFix = mySheet.AeZusatzKostenBaustelleninfrastrukturFix;
                myRow.ZkBaustellenInfrastrukturProzent = mySheet.AeZusatzKostenBaustelleninfrastrukturProzent;
                myRow.ZkErfüllungsgarantie = mySheet.AeZusatzkostenErfüllungsgarantie;
                myRow.ZkHaftrücklass = mySheet.AeZusatzkostenHaftrücklass;
                myRow.ZkProvisionDB = mySheet.AeZusatzkostenProvsionDB;
                myRow.ZkProvisionErlösFix = mySheet.AeZusatzKostenProvisionsErlösFix;
                myRow.ZkProvisionErlösProzent = mySheet.AeZusatzKostenProvisionsErlösProzent;
                myRow.ZkSkonto = mySheet.AeZusatzkostenSkonto;

                #endregion

                #region Zeitaufwand

                myRow.ZeitaufwandAdministration = mySheet.AeZeitaufwandAdminsitration;
                myRow.ZeitaufwandMontage = mySheet.AeZeitaufwandMontage;
                myRow.ZeitaufwandProduktion = mySheet.AeZeitaufwandProdutktion + mySheet.AeZeitaufwandProdukte;

                #endregion

            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "UpdateAepüDetails: " + ex.Message, true);
            }
        }

        public static void Write2Liqui(InvoiceObject i, bool oldRow, Microsoft.Office.Interop.Excel.Application exApp) {
            Workbook wb = null;
            if (GlobalVar.DebugMode) {
                return;
            }
            try {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Start Write2Liqui", false);
                bool ze = (i.ZahlArt == TpBaseObjects.Global.InvoicePayType.NachRechnungserhalt);
                string fileName = Path.Combine(Settings.Default.PathRewe + i.Rechnungsdatum.ToString(Extensions.tpDateFormat).Left(2) + @"\Liqui.xlsm");
                string sheetName = (ze ? "Ze" : "Iktp");
                string linkFileName = ""; int pos = 0;
                //Testen
                //fileName = @"T:\Rewe\12\LiquiTestEs.xlsm";

                if (Tools.FileIsInUse(fileName) | (!File.Exists(fileName))) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "Date (" + fileName + " ist geöffnet oder nicht vorhanden!", true);
                    return;
                }

                wb = exApp.Workbooks.Open(fileName);

                if (!Tools.SheetsExists(wb, sheetName)) {
                    return;
                }

                Worksheet sh = wb.Sheets[sheetName];
                ProjektLiquiSheet liqui = new ProjektLiquiSheet(sh);
                int rowNumber = 0;
                if (oldRow) {
                    myOffice.Excel.Range rCol = sh.Columns["D"];
                    myOffice.Excel.Range aCell = sh.Cells[1, "D"];
                    object toFind = i.RechnungsNummer;
                    rowNumber = GetNextRowNumber(sh, rCol, aCell, toFind, true);
                } else {
                    rowNumber = liqui.GetNextFreeRowNumber();
                }

                if (rowNumber == 0) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "oldRow:" + oldRow.ToString() + " rowNumber=0", true);
                    return;
                }

                pos = i.ProjektBezeichnung.IndexOf("p");
                linkFileName = i.Kunde + i.ProjektBezeichnung.Left((byte)pos) + Extensions.DocExt.XlsM.GetStringValue();
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "Write to Row: " + rowNumber.ToString(), false);
                myOffice.Excel.Range newRow = sh.Rows[rowNumber];
                ProjektLiquiRow lRow = new ProjektLiquiRow(newRow, ze);
                myOffice.Excel.Range kundeProjekt = newRow.Cells[1, "H"];
                string file = Settings.Default.PathKunden + i.ProjektBezeichnung.Left(2) + @"\" + (i.Kunde.Left(2) == "Wh" || i.Kunde.Left(2) == "Wg" ? i.Kunde.Left(2) : i.Kunde.Left(1)) + @"\" + linkFileName;
                string display = i.Kunde + i.ProjektBezeichnung;

                lRow.RechnungsNummer = i.RechnungsNummer;
                lRow.RechnungsDatum = i.Rechnungsdatum;
                //lRow.Kunde = i.Kunde + i.ProjektBezeichnung;
                kundeProjekt.Hyperlinks.Add(kundeProjekt, Address: file, TextToDisplay: display);
                lRow.Rechnungsempfänger = i.ReEmpfaenger;
                lRow.DebitorenKonto = i.PersonenKonto.Replace(" ", "");
                //lRow.SummeBrutto = i.RechnungsBetrag - (double)(i.SkontoBetrag != 0.0 ? Math.Abs(i.SkontoBetrag) : 0);
                if (i.SkontoBetrag != 0) {
                    myOffice.Excel.Range suBrutto = newRow.Cells[1, "P"];
                    string formel = "=";
                    if (i.AKontoBetrag != 0.0) {
                        formel += i.SummeBrutto.ToString() + "*" + (1 - (i.SkontoProzent1 / 100)).ToString() + "-" + i.AKontoBetrag.ToString();
                    } else {
                        formel += i.RechnungsBetrag.ToString() + "*" + (1 - (i.SkontoProzent1 / 100)).ToString();
                    }
                    suBrutto.FormulaR1C1 = formel.Replace(",", ".");
                    suBrutto.Font.ThemeColor = 10;
                } else {
                    lRow.SummeBrutto = i.RechnungsBetrag;
                }
                lRow.Datum = Tools.NextGoodBusinessDay(DateTime.Now);
                lRow.Firma = "Tp";
                lRow.Type = "Ar";
                if (ze) {
                    lRow.Datum = Tools.GetNextDueDate(i.Rechnungsdatum);
                    lRow.DiktatZeichen = i.Ma;
                }
                lRow.Qu = 100.0;

                myOffice.Excel.Range r1 = newRow.Cells[1, "X"];
                r1.FormulaR1C1 = "=R" + newRow.Row.ToString() + "C" + (r1.Column - 8).ToString() + "*R" + newRow.Row.ToString() + "C" + (r1.Column - 1).ToString() + "/100";

                r1 = newRow.Cells[1, "AC"];
                r1.FormulaR1C1 = "=R" + newRow.Row.ToString() + "C" + (r1.Column - 1).ToString() + "-R" + newRow.Row.ToString() + "C" + (r1.Column - 13).ToString();

                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "End Write2Liqui", false);
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "Write2Liqui: " + ex.Message, true);

            } finally {
                if (wb != null) {
                    wb.Save();
                    wb.Close();
                }
            }
        }

        #endregion

        #region Functions

        private static bool CBControlExists(CommandBar cb, string cbName) {
            foreach (CommandBarControl ctl in cb.Controls) {
                if (ctl.Caption == cbName) {
                    return true;
                }
            }
            return false;
        }

        private static bool CheckCalcSheets(Workbook wb, Worksheet sh) {
            string shName = string.Empty;

            #region Datenfelder

            #region Fehlt

            bool ret = false;
            ProjectSheet sheet = new ProjectSheet(sh, false);
            if (sheet.IsCalcSheet) {
                shName = sh.Name;
                string selCell = "";
                string msg = Resources.CheckCalcSheetsAskMissing + "\n\r\n\r";
                string msgFalsch = Resources.CheckCalcSheetsAskFalse + "\n\r\n\r";
                int lenMsg = msg.Length;

                //Kundenname
                if (sheet.Kundenname == "") {
                    selCell = Settings.Default.ZelleKundenname;
                    msg += Resources.Kundenname + "\n\r";
                }

                //Projekt
                if (sheet.Projekt == "") {
                    selCell = Settings.Default.ZelleProjektbezeichnung;
                    msg += Resources.Projektbezeichnung + "\n\r";
                }

                //Plz
                if (sheet.Plz == 0) {
                    selCell = Settings.Default.ZellePlz;
                    msg += Resources.Plz + "\n\r";
                }

                //Ort
                if (sheet.Ort == "") {
                    selCell = Settings.Default.ZelleOrt;
                    msg += Resources.Ort + "\n\r";
                }

                //Straße
                if (sheet.Straße == "") {
                    selCell = Settings.Default.ZelleStrasse;
                    msg += Resources.Straße + "\n\r";
                }

                //Kundengruppe
                if (sheet.Kundengruppe == 0) {
                    selCell = Settings.Default.ZelleKundengruppe;
                    msg += Resources.Kundengruppe + "\n\r";
                } else {
                    if ((sheet.Kundengruppe < 1) | (sheet.Kundengruppe > 5)) {
                        selCell = Settings.Default.ZelleKundengruppe;
                        msgFalsch += Resources.Kundengruppe + " (zulässiger Wert = 1 bis 5)\n\r";
                    }
                }

                //Kilometer
                if (sheet.Kilometer == 0) {
                    selCell = Settings.Default.ZelleKilometer;
                    msg += Resources.Kilometer + "\n\r";
                }

                if (msg.Length != lenMsg) {
                    msg += (msgFalsch.Length > 23 ? "\n\r" + msgFalsch : "");
                    if (MessageBox.Show(msg + "\n\r" + Resources.OhneEingabeSpeichern, shName, MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.Yes) {
                        ret = true;
                    } else {
                        myOffice.Excel.Range s1 = sh.Range[selCell];
                        ((Microsoft.Office.Interop.Excel._Worksheet)sh).Activate();
                        s1.Select();
                        ret = false;
                    }
                }

            }

            #endregion

            #region Falsch




            string selCellF = "";
            string msgF = Resources.CheckCalcSheetsAskMissing + "\n\r\n\r";
            int lenMsgF = msgF.Length;

            //Kundengruppe
            if ((sheet.Kundengruppe < 1) | (sheet.Kundengruppe > 5)) {
                selCellF = Settings.Default.ZelleKundengruppe;
                msgF += Resources.Kundengruppe + Resources.zulaessigerWert1_5 + "\n\r";
                ret = false;
            }

            #endregion

            #endregion

            return ret;
        }

        public static bool CheckKilometer(Worksheet sh, bool cancel) {
            ProjectSheet sheet = new ProjectSheet(sh, false);

            if (sheet.PlzExists) {
                if (Math.Abs(sheet.KilometerDifferenz) > 10) {
                    string msg = String.Format(Resources.CheckKilometerMsg, (sheet.KilometerDifferenz < 0 ? "" : "+") + sheet.KilometerDifferenz.ToString());
                    MessageBox.Show(msg, sh.Name, MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return true;
                }
            }
            return cancel;
        }

        public static bool CheckOrgData(Organisation org, string suchbegriff, string wbName) {
            string msg = "";
            if (org == null & suchbegriff == "") {
                msg = "Suchbegriff in Datenbank nicht gefunden.";
            }
            if (org == null & suchbegriff != "") {
                msg = "Name in Datenbank nicht gefunden.";
            }
            if (org == null) {
                MessageBox.Show(msg, wbName + " - Abnahmeprotokoll", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            return true;
        }

        private static string ChangeDokNumber(string dokName) {
            string ret = dokName;
            string ext = dokName.Substring(dokName.IndexOf("."));
            string dName = dokName.Substring(0, dokName.IndexOf("."));
            string dokNr = dName.Right(2);
            if (Tools.isNumeric(dokNr, System.Globalization.NumberStyles.Any)) {
                int dokNo = Convert.ToInt32(dName.Right(2));

                dokNo++;

                ret = dName.Substring(0, dName.Length - 2) + dokNo.ToString("00");
                ret += ext;
            }
            return ret;
        }

        public static int CountSameSheets(Workbook wb, string sheetName) {
            int lenSheetName = sheetName.Length;
            int anzSheetName = 0;
            foreach (Worksheet s in wb.Sheets) {
                if (Tools.Left(s.Name, lenSheetName) == sheetName) {
                    anzSheetName += 1;
                }
            }
            return anzSheetName;
        }

        public static int CountSheetsExists(Workbook wb, string sheetName) {
            List<string> existsSheets = new List<string>();
            foreach (Worksheet sh in wb.Sheets) {
                if (sh.Name.Contains(sheetName)) {
                    existsSheets.Add(sh.Name);
                }
            }
            return existsSheets.Count;
        }

        private static string CreateFileName(string orgName, string projekt, Extensions.DocType docType, Customer customer, Extensions.DocExt docExt, string projectNo) {
            return CreateFileName(orgName, projekt, docType, customer, docExt, projectNo, 0, DateTime.Now.Year.ToString().Right(2));
        }

        private static string CreateFileName(string orgName, string projekt, Extensions.DocType docType, Customer customer, Extensions.DocExt docExt, string projectNo, int rechnungsNummer, string jahr) {
            string fileName = string.Empty;
            if (customer == Customer.Verkauf) {
                string chkLetter = orgName.Left(1);
                string pathName = "";
                if (!Tools.isNumeric(chkLetter, System.Globalization.NumberStyles.Any)) {
                    pathName = Settings.Default.PathVerkauf + Tools.Left(orgName, 1) + @"\";
                } else {
                    pathName = Settings.Default.PathVerkauf + @"A\";
                }
                if ((orgName.Left(2) == "Wh" | orgName.Left(2) == "Wg") & orgName.Length > 3) {
                    pathName = Settings.Default.PathVerkauf + Tools.Left(orgName, 2) + @"\";
                }
                fileName = Path.Combine(pathName, orgName + Tools.Left(projekt, projekt.IndexOf("p")) + projectNo + Extensions.DocType.Verkauf.GetStringValue() + Extensions.DocType.Kundenkorrespondenz.GetStringValue() + docType.GetStringValue() + "01" + docExt.GetStringValue());
            }
            if (customer == Customer.Kunde) {
                string chkLetter = orgName.Left(1);
                string pathName = "";
                if (!Tools.isNumeric(chkLetter, System.Globalization.NumberStyles.Any)) {
                    pathName = Settings.Default.PathKunden + jahr + @"\" + Tools.Left(orgName, 1) + @"\";
                } else {
                    pathName = Settings.Default.PathKunden + jahr + @"\A\";
                }
                if ((orgName.Left(2) == "Wh" | orgName.Left(2) == "Wg") & orgName.Length > 3) {
                    pathName = Settings.Default.PathKunden + jahr + @"\" + Tools.Left(orgName, 2) + @"\";
                }
                fileName = Path.Combine(pathName, orgName + Tools.Left(projekt, projekt.IndexOf("p")) + projectNo + Extensions.DocType.Durchführung.GetStringValue() + Extensions.DocType.Kundenkorrespondenz.GetStringValue() + docType.GetStringValue() + "01" + docExt.GetStringValue());
            }
            if (customer == Customer.Verloren) {
                string pathName = Settings.Default.PathVerloren + Tools.Left(orgName, 1) + @"\";
                string chkLetter = orgName.Left(1);
                if (!Tools.isNumeric(chkLetter, System.Globalization.NumberStyles.Any)) {
                    pathName = Settings.Default.PathVerloren + jahr + @"\" + Tools.Left(orgName, 1) + @"\";
                } else {
                    pathName = Settings.Default.PathVerloren + jahr + @"\A\";
                }
                if ((orgName.Left(2) == "Wh" | orgName.Left(2) == "Wg") & orgName.Length > 3) {
                    pathName = Settings.Default.PathVerloren + jahr + @"\" + Tools.Left(orgName, 2) + @"\";
                }

                fileName = Path.Combine(pathName, orgName + Tools.Left(projekt, projekt.IndexOf("p")) + projectNo + docType.GetStringValue() + "01" + docExt.GetStringValue());
            }
            if (customer == Customer.Rewe) {
                fileName = Path.Combine(Settings.Default.PathRewe + jahr + @"\Ar\Tp\", jahr + rechnungsNummer.ToString("000") + docExt.GetStringValue());
                return fileName;
            }

            bool weiter = false;
            int maxRun = 0;
            while (weiter == false & maxRun < 98) {
                if (File.Exists(fileName)) {
                    fileName = ChangeDokNumber(fileName);
                    maxRun++;
                } else {
                    weiter = true;
                }
            }
            return fileName;
        }

        private static bool ExistsCustomsProperty(Worksheet sh, string propertyName) {
            foreach (myOffice.Excel.CustomProperty prop in sh.CustomProperties) {
                if (prop.Name == propertyName) {
                    return true;
                }
            }
            return false;
        }

        public static string GetAfterSheetName(Microsoft.Office.Interop.Excel.Application exApp, int projectNumber, Extensions.SheetType sheetType, Workbook wb) {
            string ret = string.Empty;
            try {

                ProjektWorkbook myBook = new ProjektWorkbook(projectNumber, wb);

                switch (sheetType) {

                    #region Kalkulation

                    case Extensions.SheetType.Kalkulation:
                        foreach (ProjectSheet sh in myBook.ProjektSheets) {
                            if (sh.SheetName.Contains("k")) {
                                ret = sh.SheetName;
                            }
                        }
                        return ret;

                    #endregion

                    #region Angebot

                    case Extensions.SheetType.Angebot:
                        foreach (ProjectSheet sh in myBook.ProjektSheets) {
                            if (!sh.IsCalcSheet & sh.SheetName.Contains("a") & Tools.isNumeric(sh.SheetName.Right(1), System.Globalization.NumberStyles.Any)) {
                                ret = sh.SheetName;
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("k")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        return ret;

                    #endregion

                    #region Auftragsbestaetigung

                    case Extensions.SheetType.Auftragsbestaetigung:
                        foreach (ProjectSheet sh in myBook.ProjektSheets) {
                            if (!sh.IsCalcSheet & sh.SheetName.Contains("a") & !Tools.isNumeric(sh.SheetName.Right(1), System.Globalization.NumberStyles.Any)) {
                                ret = sh.SheetName;
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (!sh.IsCalcSheet & sh.SheetName.Contains("a") & Tools.isNumeric(sh.SheetName.Right(1), System.Globalization.NumberStyles.Any)) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("k")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        return ret;

                    #endregion

                    #region Rechnung

                    case Extensions.SheetType.Rechnung:
                        foreach (ProjectSheet sh in myBook.ProjektSheets) {
                            if (sh.SheetName.Contains("f")) {
                                ret = sh.SheetName;
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("p")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("l")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("b")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("l")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (!sh.IsCalcSheet & sh.SheetName.Contains("a") & !Tools.isNumeric(sh.SheetName.Right(1), System.Globalization.NumberStyles.Any)) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (!sh.IsCalcSheet & sh.SheetName.Contains("a") & Tools.isNumeric(sh.SheetName.Right(1), System.Globalization.NumberStyles.Any)) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("k")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        return ret;

                    #endregion

                    #region Projektbericht

                    case Extensions.SheetType.Projektbericht:
                        foreach (ProjectSheet sh in myBook.ProjektSheets) {
                            if (sh.SheetName.Contains("b")) {
                                ret = sh.SheetName;
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("l")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (!sh.IsCalcSheet & sh.SheetName.Contains("a") & !Tools.isNumeric(sh.SheetName.Right(1), System.Globalization.NumberStyles.Any)) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (!sh.IsCalcSheet & sh.SheetName.Contains("a") & Tools.isNumeric(sh.SheetName.Right(1), System.Globalization.NumberStyles.Any)) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("k")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        return ret;

                    #endregion

                    #region Abnahmeprotokoll

                    case Extensions.SheetType.Abnahmeprotokoll:
                        foreach (ProjectSheet sh in myBook.ProjektSheets) {
                            if (sh.SheetName.Contains("p")) {
                                ret = sh.SheetName;
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("l")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("b")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("l")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (!sh.IsCalcSheet & sh.SheetName.Contains("a") & !Tools.isNumeric(sh.SheetName.Right(1), System.Globalization.NumberStyles.Any)) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (!sh.IsCalcSheet & sh.SheetName.Contains("a") & Tools.isNumeric(sh.SheetName.Right(1), System.Globalization.NumberStyles.Any)) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("k")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        return ret;

                    #endregion

                    #region Abrechnung

                    case Extensions.SheetType.Abrechnung:
                        foreach (ProjectSheet sh in myBook.ProjektSheets) {
                            if (sh.SheetName.Contains("r")) {
                                ret = sh.SheetName;
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("f")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("p")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("l")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("b")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("l")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (!sh.IsCalcSheet & sh.SheetName.Contains("a") & !Tools.isNumeric(sh.SheetName.Right(1), System.Globalization.NumberStyles.Any)) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (!sh.IsCalcSheet & sh.SheetName.Contains("a") & Tools.isNumeric(sh.SheetName.Right(1), System.Globalization.NumberStyles.Any)) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("k")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        return ret;

                    #endregion

                    #region Lieferschein

                    case Extensions.SheetType.Lieferschein:
                        foreach (ProjectSheet sh in myBook.ProjektSheets) {
                            if (sh.SheetName.Contains("l")) {
                                ret = sh.SheetName;
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("b")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("l")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (!sh.IsCalcSheet & sh.SheetName.Contains("a") & !Tools.isNumeric(sh.SheetName.Right(1), System.Globalization.NumberStyles.Any)) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (!sh.IsCalcSheet & sh.SheetName.Contains("a") & Tools.isNumeric(sh.SheetName.Right(1), System.Globalization.NumberStyles.Any)) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("k")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        return ret;

                    #endregion

                }
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "GetAfterSheetName: " + ex.Message, true);
            } finally {

            }
            return ret;
        }

        private static Worksheet GetCalcSheet(Workbook wb) {
            foreach (Worksheet sh in wb.Sheets) {
                ProjectSheet sheet = new ProjectSheet(sh, false);
                if (sheet.IsCalcSheet) {
                    return sh;
                }
            }
            return null;
        }

        private static int GetFirstFreeRowNumber(int copyRow, Worksheet sh) {
            myOffice.Excel.Range row = sh.Rows[copyRow];
            myOffice.Excel.Range r1 = row.Cells[1, 3];
            myOffice.Excel.Range r2 = row.Cells[1, "N"];
            while (r1.Value != null && r2.Value != null) {
                copyRow++;
                row = sh.Rows[copyRow];
                r1 = row.Cells[1, 3];
                r2 = row.Cells[1, "N"];
            }
            return copyRow;
        }

        private static myOffice.Excel.Range GetFirstAeSubFreeRow(int copyRow, Worksheet sh) {
            myOffice.Excel.Range row = sh.Rows[copyRow];
            myOffice.Excel.Range r1 = row.Cells[1, 1];
            while (r1.Value != null) {
                copyRow++;
                row = sh.Rows[copyRow];
                r1 = row.Cells[1, 1];
            }
            return row;
        }

        private static string GetLastSheetName(Workbook wb) {
            string ret = string.Empty;
            foreach (Worksheet sh in wb.Sheets) {
                ret = sh.Name;
            }
            return ret;
        }

        private static int GetMaxProjectNo(Workbook wb) {
            List<string> p = new List<string>();
            int maxNr = 0;
            foreach (Worksheet sh in wb.Sheets) {
                if (!p.Contains(sh.Name.Substring(0, 2))) {
                    p.Add(sh.Name.Substring(0, 2));
                }
            }
            maxNr = p.Count;
            return maxNr;
        }

        public static string GetNextSheetCharacter(string sheetType, Workbook wb, bool useCalcSheet) {
            char ret;
            List<Worksheet> countList = new List<Worksheet>();

            foreach (Worksheet sh in wb.Sheets) {
                ProjectSheet sheet = new ProjectSheet(sh, false);
                if (sh.Name.Contains(sheetType) & sheet.IsCalcSheet == useCalcSheet) {
                    countList.Add(sh);
                    if (sheetType == "a" & Tools.isNumeric(sh.Name.Right(1), System.Globalization.NumberStyles.Any)) {
                        countList.Remove(sh);
                    }
                }
            }
            ret = (char)(97 + countList.Count);
            return Convert.ToString(ret);
        }

        private static int GetNextRowNumber(Worksheet sh, myOffice.Excel.Range rCol, myOffice.Excel.Range activeCell, object toFind) {
            return GetNextRowNumber(sh, rCol, activeCell, toFind, false);
        }

        private static int GetNextRowNumber(Worksheet sh, myOffice.Excel.Range rCol, myOffice.Excel.Range activeCell, object toFind, bool exact) {
            int ret = 0;
            myOffice.Excel.Range r1;
            if (!exact) {
                r1 = rCol.Find(What: toFind, After: activeCell, SearchDirection: XlSearchDirection.xlNext, MatchCase: exact, LookAt: XlLookAt.xlPart);
            } else {
                r1 = rCol.Find(toFind, activeCell, SearchDirection: XlSearchDirection.xlNext, MatchCase: exact, LookAt: XlLookAt.xlWhole);
            }
            if (r1 != null) {
                ret = r1.Row;
            }
            return ret;
        }

        private static int GetNextSheetNumber(Extensions.SheetType sheetType, int projectNumber, Workbook wb) {
            int ret = 0;
            ProjektWorkbook myBook = new ProjektWorkbook(projectNumber, wb);
            int countSheets = 0;

            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                if (sh.SheetType == (int)sheetType) {
                    countSheets++;
                }
            }

            ret = countSheets + 1;
            return ret;
        }

        private static string GetPersonenkonto(string Kunde, bool isBusiness, Microsoft.Office.Interop.Excel.Application exApp) {
            string ret = "";
            Workbook wb = null;

            try {
                string fileName = Path.Combine(Settings.Default.PathQs, "80da1.xlsx");
                if (!File.Exists(fileName)) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "GetPersonenkonto: Datei " + fileName + " ist nicht vorhanden!", true);
                    ret = "Datei fehlt!";
                    return ret;
                }

                wb = exApp.Workbooks.Open(fileName, ReadOnly: true);

                if (!Tools.SheetsExists(wb, "Pkneu")) {
                    return ret;
                }

                Worksheet sh = wb.Sheets["Pkneu"];
                myOffice.Excel.Range rCol;
                myOffice.Excel.Range activeCell;
                int rowNumber = 0;
                object toFind = "";
                if (isBusiness) {
                    if (Kunde.Left(2) == "Wh") {
                        return "230 230";
                    }
                    if (Kunde.Left(2) == "Wg") {
                        return "230 901";
                    }
                    if (Kunde.Contains("Thyssen") | Kunde.Contains("Interlux")) {
                        return "230 090";
                    }
                    rCol = sh.Columns[3];
                    activeCell = sh.Cells[2, 3];
                    toFind = Kunde;
                    rowNumber = GetNextRowNumber(sh, rCol, activeCell, toFind);
                    if (rowNumber != 0) {
                        myOffice.Excel.Range r1 = sh.Rows[rowNumber];
                        myOffice.Excel.Range r2 = r1.Cells[1, "B"];
                        ret = ((string)Convert.ToString(r2.Value)).Left(3) + " " + ((string)Convert.ToString(r2.Value)).Right(3);
                        return ret;
                    }

                    return "230 900";
                }

                rCol = sh.Columns[3];
                activeCell = sh.Cells[2, 3];
                toFind = "Privatkunden " + Kunde.Left(1);
                rowNumber = GetNextRowNumber(sh, rCol, activeCell, toFind);
                if (rowNumber != 0) {
                    myOffice.Excel.Range r1 = sh.Rows[rowNumber];
                    myOffice.Excel.Range r2 = r1.Cells[1, "B"];
                    ret = ((string)Convert.ToString(r2.Value)).Left(3) + " " + ((string)Convert.ToString(r2.Value)).Right(3);
                    return ret;
                }

            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "GetPersonenkonto: " + ex.Message, true);
                return ret;
            } finally {
                if (wb != null) {
                    //wb.Save();
                    wb.Close(SaveChanges: false);
                }
            }
            return ret;
        }

        private static int GetProjectNumber(string SheetName) {
            if (SheetName == null) {
                return 0;
            }

            int n1 = (int)(Tools.isNumeric(SheetName.Substring(1, 1), System.Globalization.NumberStyles.Any) ? Convert.ToInt32(SheetName.Substring(1, 1)) : 0);
            if (n1 != 0) {
                if (Tools.isNumeric(SheetName.Substring(2, 1), System.Globalization.NumberStyles.Any)) {
                    n1 = (n1 * 10) + Convert.ToInt32(SheetName.Substring(2, 1));
                }
            }

            return n1;
        }

        public static int GetRechnungsNummer(InvoiceObject i, string project, Microsoft.Office.Interop.Excel.Application exApp) {
            Workbook wb = null;
            int ret = 0;
            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "GetRechnungsNummer: " + i.ToString(), false);
            string fileName = Path.Combine(Settings.Default.PathRewe + i.Rechnungsdatum.ToString(Extensions.tpDateFormat).Left(2) + @"\", "Ar" + i.Rechnungsdatum.ToString(Extensions.tpDateFormat).Left(2) + Extensions.DocExt.XlsM.GetStringValue());
            //Testen
            //fileName = @"T:\Rewe\12\Ar12TestEs.xlsm";
            LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "GetRechnungsNummer: FileName(" + fileName + ")", false);
            try {
                if (Tools.FileIsInUse(fileName) | (!File.Exists(fileName))) {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "Datei " + fileName + " ist geöffnet oder nicht vorhanden!", true);
                    return -1;
                }

                wb = exApp.Workbooks.Open(fileName);

                if (!Tools.SheetsExists(wb, "Tp")) {
                    return -2;
                }

                Worksheet sh = wb.Sheets["Tp"];
                LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "GetRechnungsNummer: Sheetname(" + sh.Name.ToString() + ")", false);
                ProjektArSheet arSheet = new ProjektArSheet(sh);
                int newRowNr = 0;
                if (i.RechnungsNummer == 0) {
                    newRowNr = arSheet.GetNextFreeRowNumber(i.Rechnungsdatum.Year, i.Rechnungsdatum.Month);
                } else {
                    myOffice.Excel.Range rCol = sh.Columns["M"];
                    myOffice.Excel.Range aCell = sh.Cells[1, "M"];
                    object toFind = (i.RechnungsNummer - 1);
                    newRowNr = GetNextRowNumber(sh, rCol, aCell, toFind, true);
                    arSheet.NewInvoiceNumber = i.RechnungsNummer;
                }

                myOffice.Excel.Range r1 = sh.Rows[newRowNr];
                InvoiceNumberRow newRow = new InvoiceNumberRow(r1);
                int newInvoiceNumber = arSheet.NewInvoiceNumber;

                //newRow = new InvoiceNumberRow(r1);
                newRow.DebitorenKonto = i.PersonenKonto.Replace(" ", "");
                newRow.Kunde = i.Kunde;
                newRow.Projekt = i.AFAbText;
                newRow.RechnungsDatum = i.Rechnungsdatum;
                newRow.Rechnungsempfänger = i.ReEmpfaenger;
                newRow.RechnungsNummer = newInvoiceNumber;
                newRow.SachKonto = i.SachKonto;
                newRow.SummeNetto = i.SummeNetto;
                newRow.Ust = i.Ust;
                newRow.VjProzent = i.AufwandVorjahr;

                if (i.AKontoBetrag != 0 && i.IsFirstInvoice) {

                    sh = wb.Sheets["Tpak"];
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, logType, logArea, "GetRechnungsNummer: Sheetname(" + sh.Name.ToString() + ")", false);
                    arSheet = new ProjektArSheet(sh);
                    newRowNr = arSheet.GetNextFreeRowNumber(i.Rechnungsdatum.Year, i.Rechnungsdatum.Month);
                    r1 = sh.Rows[newRowNr];
                    newRow = new InvoiceNumberRow(r1);
                    newRow.DebitorenKonto = i.PersonenKonto.Replace(" ", "");
                    newRow.Kunde = i.Kunde;
                    newRow.Projekt = i.AFAbText;
                    newRow.RechnungsDatum = i.Rechnungsdatum;
                    newRow.Rechnungsempfänger = i.ReEmpfaenger;
                    newRow.RechnungsNummer = newInvoiceNumber;
                    newRow.SachKonto = i.SachKonto;
                    newRow.SummeNetto = i.AKontoBetrag / (1 + (i.UstPercent / 100));
                    newRow.Ust = i.AKontoBetrag - (i.AKontoBetrag / (1 + (i.UstPercent / 100)));
                    newRow.VjProzent = i.AufwandVorjahr;


                }

                ret = newInvoiceNumber;


            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "GetRechnungsNummer: " + ex.Message, true);
                return ret;
            } finally {
                if (wb != null) {
                    if (!GlobalVar.DebugMode) {
                        wb.Save();
                    }
                    wb.Close();
                }
            }
            return ret;
        }

        public static string GetSheetName(Extensions.SheetType sheetType, int projectNumber, Workbook wb, Worksheet sh) {

            ProjectSheet sheet = new ProjectSheet(sh, false);
            string ret = "P" + sheet.ProjektNummer.ToString();
            switch (sheetType) {
                case Extensions.SheetType.Kalkulation:
                    ret += "k" + GetNextSheetNumber(sheetType, projectNumber, wb).ToString();
                    break;
                case Extensions.SheetType.Angebot:
                    ret += "a" + sheet.CalcNumber.ToString();
                    break;
                case Extensions.SheetType.Auftragsbestaetigung:
                    ret += "a" + sheet.CalcCharacter;
                    break;
                case Extensions.SheetType.Rechnung:
                    ret += "f" + GetNextSheetNumber(sheetType, projectNumber, wb).ToString();
                    break;
                case Extensions.SheetType.Projektbericht:
                    ret += "b" + GetNextSheetNumber(sheetType, projectNumber, wb).ToString();
                    break;
                case Extensions.SheetType.Abnahmeprotokoll:
                    ret += "p" + GetNextSheetNumber(sheetType, projectNumber, wb).ToString();
                    break;
                case Extensions.SheetType.Abrechnung:
                    ret += "r";
                    break;
                case Extensions.SheetType.Lieferschein:
                    ret += "l" + GetNextSheetNumber(sheetType, projectNumber, wb).ToString();
                    break;

            }

            return ret;
        }

        public static TpMenu GetTpMenu(Worksheet sh) {
            int colNo = 1;
            int row = 1;
            TpMenu myMenu = new TpMenu();
            int groupIndex = 1;
            int menuIndex = 0;

            try {

                // Lese alle Groupdaten ein !!!
                myOffice.Excel.Range r1 = sh.Cells[row, "CE"];
                myOffice.Excel.Range r2;
                string t1 = Tools.Nz(Convert.ToString(r1.Value), "");
                if (t1 != "" | t1.Length > 0) {
                    TpMenuGroup g1 = new TpMenuGroup(t1, groupIndex);
                    myMenu.AddGroup(g1);
                    //Lese alle Gruppen ein
                    bool weiter = false;
                    while (!weiter) {

                        colNo = r1.Column + 1;
                        r1 = sh.Cells[1, colNo];
                        t1 = Tools.Nz(Convert.ToString(r1.Value), "");
                        if (t1 != "") {

                            if (myMenu.GetMyGroupIndex(t1) == 0) {
                                groupIndex += 1;
                                g1 = new TpMenuGroup(t1, groupIndex);
                                myMenu.AddGroup(g1);
                            }
                        } else {
                            weiter = true;
                        }
                    }

                    //Lese alle Menüpunkte ein.
                    row = 2;
                    r1 = sh.Cells[row, "CE"];
                    colNo = r1.Column - 1;
                    weiter = false;
                    while (!weiter) {

                        colNo += 1;
                        r1 = sh.Cells[row, colNo];
                        t1 = Tools.Nz(Convert.ToString(r1.Value), "");
                        r2 = sh.Cells[row - 1, colNo];

                        if (t1 != "") {

                            int index = myMenu.GetMyGroupIndex(r2.Value);
                            if (myMenu.MyGroups[index - 1].GetMyMenuItemIndex(t1) == 0) {
                                menuIndex += 1;
                                TpMenuGroupItem gi1 = new TpMenuGroupItem(r2.Value, t1, colNo, menuIndex);
                                myMenu.MyGroups[index - 1].AddItems(gi1);
                            }
                        } else {
                            weiter = true;
                        }
                    }
                }
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "GetTpMenu: " + ex.Message, true);
            }
            return myMenu;
        }

        private static myOffice.Word.Document GetWordDocument(myOffice.Word.Application appWord, string fileName, string vorlage) {
            myOffice.Word.Document myDoc = null;
            try {
                if (vorlage != null | vorlage != string.Empty) {
                    myDoc = appWord.Documents.Add(Template: vorlage);
                    return myDoc;
                } else {
                    LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "GetWordDocument: myDoc is null", true);
                    myDoc = appWord.Documents.Add();
                    return myDoc;
                }
            } catch (Exception ex) {
                LogDataManager.SaveLog(Contracts.MyUser.UserID, errType, logArea, "GetWordDocument: " + fileName + " (" + vorlage + "): " + ex.Message, true);
            }
            return myDoc;
        }

        public static InvoiceObject GetNewInvoiceWithData(ProjectSheet inv, ProjectSheet abr, Organisation org, int rechnungsNummer, Microsoft.Office.Interop.Excel.Application exApp) {

            TpBaseObjects.Global data = new TpBaseObjects.Global();

            InvoiceObject i = new InvoiceObject(rechnungsNummer);

            //Auftraggeberdaten
            i.ProjektBezeichnung = abr.Projekt;
            i.Auftraggeber = (org.Prefix != null ? org.Prefix + " " : "") + org.OrgName + " " + org.ZusätzeFW + " " + org.RechtsForm;
            i.Kunde = org.OrgName;
            i.CheckAuftraggeberPerson = false;
            i.MyPersons = org.MyPersons;
            i.AuftraggeberStrasse = org.Straße;
            i.AuftraggeberPlzOrt = org.Plz + " " + org.Ort;
            i.AuftraggeberMail = org.EMail;
            //Rechnungsempfänger - wird standardmäßig nicht befüllt - muss vom user ausgefüllt werden

            //Beilagen
            i.BeilageCheckLS = false;
            i.BeilageLSDate1 = new DateTime(1900, 1, 1);
            i.BeilageLSDate2 = new DateTime(1900, 1, 1);
            i.BeilageLSDate3 = new DateTime(1900, 1, 1);
            i.BeilageAPDate1 = new DateTime(1900, 1, 1);
            i.BeilageAPDate2 = new DateTime(1900, 1, 1);
            i.BeilageAPDate3 = new DateTime(1900, 1, 1);

            if (abr.LsDate1.Year > 1900)
                i.BeilageLSDate1 = abr.LsDate1;
            if (abr.LsDate2.Year > 1900)
                i.BeilageLSDate2 = abr.LsDate2;
            if (abr.LsDate3.Year > 1900)
                i.BeilageLSDate3 = abr.LsDate3;
            i.BeilageCheckAP = false;
            if (abr.ApDate1.Year > 1900)
                i.BeilageAPDate1 = abr.ApDate1;
            if (abr.ApDate2.Year > 1900)
                i.BeilageAPDate2 = abr.ApDate2;
            if (abr.ApDate3.Year > 1900)
                i.BeilageAPDate3 = abr.ApDate3;
            i.BeilageZusatzTextCheck = false;
            i.BeilageZusatzText = "";
            i.BeilageDateiCheck = false;
            i.BeilageDatei = "";

            //Rechnung
            i.Rechnungsdatum = DateTime.Now.Date;
            i.RechnungVersand = TpBaseObjects.Global.InvoiceSendType.Persoenlich;
            i.Ma = data.UserName;
            i.Ma2 = "Pn"; //defaultValue
            i.AufwandVorjahr = 0.0;
            i.Ust = inv.Umsatzsteuer;
            i.UstPercent = inv.UmsatzsteuerProzent;
            i.SummeBrutto = inv.SummeBrutto;
            i.SummeNetto = inv.SummeNetto;

            //Auftragsdaten
            i.AFAuftragTelefon = false;
            i.AFAuftragTelefonDatum = abr.AuftragsDatum;
            i.AFAuftragPers = false;
            i.AFAuftragPersDatum = abr.AuftragsDatum;
            i.AFAbCheck = false;
            i.AFAbDatum = abr.AuftragsBestaetigung;
            i.AFAbText = abr.Projekt;
            i.AFDurchfuehrungBis = Tools.NextGoodBusinessDay(DateTime.Now);
            i.AFDurchfuehrungVon = abr.DurchfuehrungVon;

            if (i.AFDurchfuehrungVon == new DateTime(1900, 1, 1)) {
                i.AFDurchfuehrungVon = i.AFDurchfuehrungBis;
            }

            if (i.SummeBrutto > 0) {
                //Zahlung
                i.ZahlArt = TpBaseObjects.Global.InvoicePayType.BarNachMontage;
                i.AKontoDatum = abr.AkontoDatum;
                i.AKontoText = abr.AkontoBeleg;
                i.AKontoBetrag = Math.Abs(abr.Akonto);
                //i.RechnungsBetrag = inv.SummeBrutto;
                i.SkontoProzent1 = abr.SkontoProzentSatz; // (inv.SkontoProzentSatz != 0 ? inv.SkontoProzentSatz : abr.SkontoProzentSatz);
                i.SkontoTage1 = abr.SkontoFrist; //(inv.SkontoFrist != 0 ? inv.SkontoFrist : abr.SkontoFrist);
                i.SkontoTage2 = abr.ZahlungsFrist; // (inv.ZahlungsFrist != 0 ? inv.ZahlungsFrist : abr.ZahlungsFrist);
                //i.SkontoBetrag = abr.SkontoBetrag; // (inv.SkontoBetrag != 0 ? inv.SkontoBetrag : abr.SkontoBetrag);
                i.SkontoBetrag = inv.SummeBrutto * (abr.SkontoProzentSatz / 100);
                i.SkontoFrist = abr.SkontoFrist;

                if (abr.Akonto != 0) {
                    i.AKontoDatum = abr.AkontoDatum;
                    i.AKontoText = abr.AkontoBeleg;
                    i.AKontoBetrag = Math.Abs(abr.Akonto);
                }
            }
            //Buchhaltung
            i.PersonenKonto = GetPersonenkonto(i.Kunde, i.IsBusiness, exApp);
            i.SachKonto = (i.Ust != 0 ? "400 00" : "400 30");


            //Dateien
            LoadFiles(i);

            return i;
        }

        private static bool IsCalcSheet(Worksheet sh) {
            ProjectSheet sheet = new ProjectSheet(sh, false);
            return sheet.IsCalcSheet;
        }

        private static string ReadSignature(string user) {
            string appDataDir = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\Microsoft\\Signatures";
            string signature = string.Empty;
            DirectoryInfo diInfo = new DirectoryInfo(appDataDir);

            if (diInfo.Exists) {
                FileInfo[] fiSignature = diInfo.GetFiles(user + ".htm");

                if (fiSignature.Length > 0) {
                    StreamReader sr = new StreamReader(fiSignature[0].FullName, Encoding.Default);
                    signature = sr.ReadToEnd();

                    if (!string.IsNullOrEmpty(signature)) {
                        string fileName = fiSignature[0].Name.Replace(fiSignature[0].Extension, string.Empty);
                        signature = signature.Replace(fileName + "-Dateien/image001.png", "http://www.tec-fachmarkt.at/extern/signaturen/signatur.png");
                    }
                }
            }
            return signature;
        }

        #endregion

    }
}
